Create a gist now

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Exporting and Importing Postgres Databases using gzip
# This is just a cheat sheet:
# On production
sudo -u postgres pg_dump database | gzip -9 > database.sql.gz
# On local
scp -C production:~/database.sql.gz
dropdb database && createdb database
gunzip < database.sql.gz | psql database
# This guide shows you how to use gzip when pulling down a production database to your local environment
#
# A production database dump can be very large, like 1.5GB
# But database dumps contains a lot of empty space
# Gzipping the database can take the size from 1.5GB down to as low as 50MB
# But you are left zipping and unzipping all the time
#
# Follow these steps to avoid ever creating a large .sql file in the first place
# exporting and importing directly with the gzipped version
# For this example, the production server is named "production"
# On the production server:
# Navigate to your home directory.
# If this next command fails, it is because you don't have permission to switch to the postgres user
# If so, you will need to login as root before you can run this next command
sudo -u postgres pg_dump DATABASENAME | gzip -9 > DATABASENAME.sql.gz
# You should now have a file in your home directory, and you should be the owner
ls -alh ~/DATABASENAME.sql.gz
# You should see yourself as the owner
# $ -rw-r--r-- 1 brock users 45M Oct 15 12:00 DATABASENAME.sql.gz
# If you are not the owner, or if root is the owner,
# you'll need to change the ownership to yourself before you'll be able download it
# as root:
# chown YOUR_USERNAME_ON_PRODUCTION_SERVER: DATABASENAME.sql.gz
# Note the colon after your username
# Log out of the production server and go back to your local machine
# Use scp to download (-C uses compression for faster downloads)
scp -C production:~/DATABASENAME.sql.gz
# If you already have a local database, the .sql file might complain if you try to import it.
# This can be due to duplicate keys, or if the SQL import attempts to create the table that already exists, etc.
# Only delete the database if you are sure, but I do this all the time
# On OSX, run these commands
drop_db DATABASENAME
create_DB DATABASENAME
# On Linux, the commands are typically
dropdb DATABASENAME
createdb DATABASENAME
# Now re-import the database directly from the gzipped file:
gunzip < DATABASENAME.sql.gz | psql DATABASENAME
# The file remains gzipped both on prod and on your local copy
@NadirZenith

This comment has been minimized.

Show comment
Hide comment
@NadirZenith

NadirZenith Jul 12, 2016

thanks! some documentation too ..

thanks! some documentation too ..

@brunifrancesco

This comment has been minimized.

Show comment
Hide comment
@brunifrancesco

brunifrancesco Jan 23, 2018

Or you could dump the db, compress the archive and transferring it using just one command:

pg_dump -C -h localhost -U <user> <db> -W | gzip -c | ssh user@host.domain.com "cat - > /home/<user>/dump.sql.gz

And, if you go further, you'd decompress it while catting..

cheers,
FB

Or you could dump the db, compress the archive and transferring it using just one command:

pg_dump -C -h localhost -U <user> <db> -W | gzip -c | ssh user@host.domain.com "cat - > /home/<user>/dump.sql.gz

And, if you go further, you'd decompress it while catting..

cheers,
FB

@adamczi

This comment has been minimized.

Show comment
Hide comment
@adamczi

adamczi Feb 26, 2018

How about just using -Fc to get a compressed dump? As per man:

Output a custom-format archive suitable for input into pg_restore. Together with the directory output format, this is the most flexible output format in that it allows manual selection and reordering of archived items during restore. This format is also compressed by default.

adamczi commented Feb 26, 2018

How about just using -Fc to get a compressed dump? As per man:

Output a custom-format archive suitable for input into pg_restore. Together with the directory output format, this is the most flexible output format in that it allows manual selection and reordering of archived items during restore. This format is also compressed by default.

@Avolition

This comment has been minimized.

Show comment
Hide comment
@Avolition

Avolition Feb 27, 2018

Is it possible to do the same but for individual tables and queried subsets? Converting a SELECT x FROM table TO csv and gzipping on the remote database, before downloading? I've asked this question on stackoverflow here

Is it possible to do the same but for individual tables and queried subsets? Converting a SELECT x FROM table TO csv and gzipping on the remote database, before downloading? I've asked this question on stackoverflow here

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