How to Postgres

Sometimes we need a little help :)

by Risa

General Postgres navigation

To enter

psql
# if it says a db doesn't exist, then list the databases available
psql -l   # thats an L
# choose a db listed (eg db_prod)
psql db_prod  # gets you in as db_prod)

Inside postgres, the database name is the prompt name. So if db_prod is your database name, the prompt will then be:

db_prod=#   <-- this is a standard postgres prompt

Oh god how do I exit??

\q               #=> quits
\c               #=> tells you which db youre in and as what user
\l               #=> lists the dbs
\c db_name       #=> switches db to db_name
\dt              #=> display tables
\dx              #=> display extensions
\d table_name    #=> displays columns in table
\x auto          #=> makes things look pretty

Some standard sql queries.

select * from some_table;
select count(*) from some_table;
select * from users where id='25';

Export commands

Need to get a dump from some other server (A) to put to this server (B)? This section will help. The command will get you a full database with all the information in it, from tables, extensions, keys, etc.

Check to see if Postgres on A can be accessed by other machines with the following:

netstat -plunt

# output will look like this if it's open to the world (see command 1)
tcp        0      0   0.0.0.0:5432            0.0.0.0:*               LISTEN      -

# if closed it'll be like this  (see command 2)
tcp        0      0 127.0.0.1:5432            0.0.0.0:*               LISTEN      -

See above if you need to do command 1 or command 2. Choose your own adventure based on them.

> command 1

Run this on server B. (B is grabbing the dump from A)

pg_dump -h 123.123.123.123 -p 5432 -U rails -d DATABASE_NAME -Fc -x -O > todaysdate.dump
# -h 123.123.123.123  => IP address of the sever A
# -p 5432             => port that postgres is running on over there
# -U rails            => or whatever the postgres user is (check database.yml)
# -d DATABASE_NAME    => fill this in with the database you want to access
# -Fc                 => file will be compressed and unreadable to humans
# -x                  => no privileges will be transferred
# -O                  => no ownership will be transferred
# >                   => means its outputting
# todaysdate.dump     => name the file with date. add .dump to remind you its compressed

You'll most likely have to put in a password for access. Depending on size of DB, this can be 1 second or 15 minutes.

> command 2

Sorry yo, this is gonna be painful. The dump needs to be created on A itself

pg_dump -U rails -d DATABASE_NAME -Fc -x -O > todaysdate.dump
# see above for details on what the flags/options mean
# adjust the user (-U rails) and the database (-d DATABASE_NAME) as necessary

You'll either need to scp the file from A to B (1 step and faster) or you can use Cyberduck and transfer the file to your machine then upload it to server B (easier but more steps and slower). The directions will be to scp.

To secure copy (scp) the file, on A, run this:

scp -r file_or_dir user@IP:/path/to/where/you/want/file

# example: i am in /var/www/apps/12345/current/backup/ on A
# and I want to move all the files in this dir to B
scp -r . [email protected]:/home/rails/backup/
# -r                   => recursive
# .                    => all the files in this dir
# rails                => username you'd log in with on B
# 222.222.222.222      => B's IP address
# :/home/rails/backup/ => location I want those files

Import commands

This is a lot easier. You're now on server B where you want to put the data in. You've followed the above directions and are ready to go! Get yourself to the directory where you put the backup (todaysdate.dump)

pg_restore -d DATABASE_NAME < todaysdate.dump

# alternatively you can specify flags again for no privileges/no owner
pg_restore -d DATABASE_NAME -x -O < todaysdate.dump

# want only the data and no priv, no owner?
pg_restore -d DATABASE_NAME -a -x -O < todaysdate.dump
# -a      => data only flag

Expect to see errors. If they are errors about foreign keys already existing or roles don't exist, you can ignore them as they are warnings and do no harm. However, if you get errors about incorrect syntax or whatnot, be mindful of these! Most likely the data did not transfer over. Spot check them against server A.