Installing and managing postgreSQL and loading data into postgreSQL

Installing, managing postgreSQL and loading data into postgreSQL

postgreSQL maybe pre-installed with many versions of Linux – if so you’ll see a postgres user

sudo -i -u postgres

If you get an error, and the postgres user does not exist then – install postgreSQL as root
Directions are here:
https://www.postgresql.org/download/linux/ubuntu/
Or here
https://www.digitalocean.com/community/tutorials/how-to-install-and-use-postgresql-on-ubuntu-16-04

Once you have installed it – you can certainly

sudo -i -u postgres

To use the command line admin tool type:

psql

To create a table:

CREATE TABLE playground (
    equip_id serial PRIMARY KEY,
    type varchar (50) NOT NULL,
    color varchar (25) NOT NULL,
    location varchar(25) check (location in ('north', 'south', 'west', 'east', 'northeast', 'southeast', 'southwest', 'northwest')),
    install_date date
);To display a table: \d playground

How to get help

\help
\?

Commands like \? will paginate when there is more text than fits on a line like you just piped to “| more” so just hit the space bar

Loading Data Into postgreSQL

Well there are probably a bunch of ways, or even more but, here are two
If you are using postgreSQL as an Amazon AWS RDS service use SCT and DMS as described here:
https://docs.aws.amazon.com/dms/latest/sbs/CHAP_RDSOracle2PostgreSQL.html
The link above will show you have to migrate from Oracle to postgreSQL but the process is similar for other DBs in the AWS RDS world.
If you just want to load some CSV files using python look here:
https://www.dataquest.io/blog/loading-data-into-postgres/

Bulk loading postreSQL with pg_bulkload

pg_bulkload

This program is used to load the data. Internally, it invokes PostgreSQL’s user-defined function called pg_bulkload() and perform the loading. pg_bulkload() function will be installed during pg_bulkload installation.
You can use pg_bulklad by the following three steps:

  1. Edit control file “sample_csv.ctl” or “sample_bin.ctl” that includes settigs for data loading. You can specify table name, absolute path for input file, description of the input file, and so on.
  2. Assume there is a directory $PGDATA/pg_bulkload, in that load status files are created.
  3. Execute command with a control file as argument. Relative path is available for the argument.$ pg_bulkload sample_csv.ctl NOTICE: BULK LOAD START NOTICE: BULK LOAD END 0 Rows skipped. 8 Rows successfully loaded. 0 Rows not loaded due to parse errors. 0 Rows not loaded due to duplicate errors. 0 Rows replaced with new rows.

https://pgxn.org/dist/pg_bulkload/3.1.8/doc/pg_bulkload.html
More from LonzoDB on AWS

Leave a Comment

Scroll to Top