CODEWITHSUNDEEP
sqlpg-dump
Chris Adams
Chris Adams

Reputation: 2871

The most efficient way to move psql databases

What is the most efficient, secure way to pipe the contents of a postgresSQL database into a compressed tarfile, then copy to another machine?

This would be used for localhosting development, or backing up to a remote server, using *nix based machines at both ends.

Upvotes: 0

Views: 335

Answers (2)

bortzmeyer
bortzmeyer

Reputation: 35459

pg_dump is indeed the proper solution. Be sure to read the man page. In Espo's example, some options are questionable (-x and -D) and may not suit you.

As with every other database manipulation, test a lot!

Upvotes: 0

Espo
Espo

Reputation: 41929

This page has a complete backup script for a webserver, including the pg_dump output.

Here is the syntax it uses:

BACKUP="/backup/$NOW"
PFILE="$(hostname).$(date +'%T').pg.sql.gz"
PGSQLUSER="vivek"
PGDUMP="/usr/bin/pg_dump"

$PGDUMP -x -D -U${PGSQLUSER} | $GZIP -c > ${BACKUP}/${PFILE}

After you have gzipped it, you can transfer it to the other server with scp, rsync or nfs depending on your network and services.

Upvotes: 1

Related Questions