Postgresql – Streaming PostgreSQL pg_dump to S3

amazon s3postgresql

Is it possible or advisable to stream/pipe pg_dump output to S3?

We are dumping large datasets to our instance and the database size is big. So trying to optimize for local disk space (avoid temp space for dump) and create the backup straight on S3.

We have a PostgreSQL v9.6.3 on Ubuntu 16.04.

Best Answer

You can use s3's multipart upload feature to stream the dump as it's being generated. However that is likely to be error prone and less than reliable. A better approach is to create an ephemeral EBS volume, dump your database to it. And then upload the compressed backup to s3/Glacier if that is where it needs to go.

If you are wanting a backup for point in time recovery doing a pg_basebackup to an EBS volume and archiving the WAL stream from the point after the backup means you could cut the time to recovery without keeping a full replica node. If your concern is availability then setting up replication is the way to go. Although you will still want backups.

Replication is not backup, if someone drops a table on the Origin it will be dropped on the Replica; so you still need PITR or checkpoint backups.

Related Solutions

Postgresql – How to see active connections and “current activity” in PostgreSQL 8.4

OK, got it from someone else. This query should do the trick:

select *
from pg_stat_activity
where datname = 'mydatabasename';

PostgreSQL – Fix pg_dump and pg_restore Input File Does Not Appear to Be a Valid Archive

You are dumping in plain SQL format which was designed to feed to psql. This is not recognized by pg_restore.

cat db.txt | psql dbname

Should do the trick

Best Answer

Related Solutions

Postgresql – How to see active connections and “current activity” in PostgreSQL 8.4

PostgreSQL – Fix pg_dump and pg_restore Input File Does Not Appear to Be a Valid Archive

Related Topic