8

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.

Improve this question
2
  • How are you performing the rest of your backups? Both Amanda and Backula have S3 and postgres functionality.
    – Spooler
    Dec 4, 2017 at 23:32
  • I presume, you've already tried to mount s3 bucket to your EC2 filesystem and choose as destination folder for backup. Did it work?
    – Andrey Lebedenko
    Nov 14, 2018 at 12:25

3 Answers 3

Reset to default
6

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.

Improve this answer
4
  • Thanks a lot for your response. Started using wal-e for WAL and base backup. Works great. But does wal-e stream the backup directly to S3, meaning it does not require any local (temp) disk space? Or it writes locally first and then writes to S3?
    – kapso
    Dec 12, 2017 at 6:12
  • It is using the wal segments that postgres is writing to disk already. They are a fixed size (16MB) and are written out to disk before postgres flushes pending table changes to disk. Thus the name Write Ahead Log.
    – Larry
    Dec 13, 2017 at 4:06
  • Ah got it, yea that makes sense, thanks a lot. How about "wal-e backup-push", can local disk space (or lack of) have an impact on this process?
    – kapso
    Dec 13, 2017 at 11:02
  • Yes. Running out of disk space on a database server is not a good thing. You can control how much space is used for WAL segments ( keep_wal_segments ) but if the server does not have room to write WAL it will die since it cannot meet it's durability guarantees.
    – Larry
    Dec 14, 2017 at 23:02
17

pg_dump streaming directly to S3 seems to work fine. I have 350GB database and do not want to create temp additional drives. You need to make sure that the multipart chunk size is big enough, otherwise I ran into 'too many segments' issue. With AWS CLI the commands:

aws configure set default.s3.multipart_chunksize 200MB 
time sudo -u postgres pg_dump -Z 9 -v DB_NAME |aws s3 cp - s3://BUCKET/DB_NAME.dump.gz

With my db it took about 8 hours and result was 130GB file in S3. Now restoring has to be done with psql, as pg_restore does not like plain sql dumps what the command above creates. I could not use custom format there, as this wants to create directories which cannot (probably?) be piped away.

Finally restoring same way, without intermediate file saving. Note that I had to uncompress the data before psql using zcat:

wget -O - 'https://S3-URL/BUCKET/DB_NAME.dump.gz' |zcat |sudo -u postgres psql DB_NAME

The restoring seems to take about same time (~8 hours) as dumping, probably depends where and how big is your server (AWS or somewhere else, mine is outside AWS).

Improve this answer
1

No, it's not wise. Instead set up actual replication which PostgreSQL supports. I would use the subscriber model, but you can also do WAL-log shipping to s3 if you want using archive_command.

However, that's mostly unnecessary. I wouldn't consider it unless I had more a special use-case.

I would upgrade to 10.1 and jump on Logical Replication with the subscriber model.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .