A quick reminder on how to perform a Postgres SQL dump.

Postgres_SQL_Dump.md

Use pg_dump to create a dev copy of a DB

Steps

1. Perform pg_dump -> gzip output -> save

   • pg_dump DB_NAME --username=DB_USER | gzip > /location/to/backup.gz
   • Note: if you get "FATAL: Ident authentication failed..." you'll need to look in pg_hba.conf. See below for more info.

2. Unzip -> import into new database

   • cat /location/to/backup.gz | gunzip | psql DB_NAME
   • Note: DB_NAME needs to exist. createdb -T template0 --username=DB_USER DB_NAME

pg_hba.conf info

If in step one you were expecting a password prompt, there are two likely possibilities. Assuming your pg_hba.conf file looks like this (probably in /etc/postgresql/VERSION/main/pg_hba.conf):

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             postgres                                peer
local   all             all                                     peer
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5

Then (i) supply the -h localhost parameter so that you don't connect via Unix sockets (lines 1 and 2) but rather via TCP/IP (lines 3 and 4). Or alternativly (ii) add/change the "Method" column to md5 for the user/connection type you're using.