Skip to content

Instantly share code, notes, and snippets.

@atenni

atenni/Postgres_SQL_Dump.md

Last active April 26, 2023 16:48
Show Gist options
  • Star 6 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
  • Save atenni/a73cfbec1b7fd367b833 to your computer and use it in GitHub Desktop.
Save atenni/a73cfbec1b7fd367b833 to your computer and use it in GitHub Desktop.
Download ZIP
A quick reminder on how to perform a Postgres SQL dump.
Raw
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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment