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:
Once you have installed it – you can certainly
sudo -i -u postgres
To use the command line admin tool type:
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
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:
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:
Bulk loading postreSQL with 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:
- 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.
- Assume there is a directory
$PGDATA/pg_bulkload, in that load status files are created.
- 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.