Backup and restore postgresql data folder directly

Question

Till now I've been backing up my postgresql data using pg_dump, which exports the data to an sql file mydb.sql, and then restoring from that sql file using psql -U user -d db < mydb.sql.

For one reason or another it would be more convenient to restore the database content more directly, in an environment where psql does not exist... specifically on a host server where postgresql is installed in a docker container running on the host, but not on the host itself.

My plan is to back up the content of /var/lib/postgresql/data/ to a tar file, and when required (e.g. when a new server is created that hosts the postgresql container) just restore that to the same path. The folder /var/lib/postgresql/data/ in the docker container is mapped to a folder on the host server, so I would create this backup on the host, not inside the postgres container.

Is this a valid approach? Any "gotchas"? And are there any subfolders within /var/lib/postgresql/data/ that I can exclude from the tar file? I don't want to back up mere 'housekeeping' information.

Laurenz Albe · Accepted Answer · 2018-09-18 13:52:56Z

3

You can do that, but you have to do it properly if you don't want your database to become corrupted.

Either stop PostgreSQL before copying the data directory or follow the instructions from the documentation for an online backup.

answered Sep 18, 2018 at 13:52

Laurenz Albe

257k22 gold badges311 silver badges387 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

drmrbrewer Over a year ago

Thanks, that link is a great help. It looks like pg_basebackup might be the easiest way to go about it (I can shell into the postgres container to do this) and then use the output of that to restore the database.

drmrbrewer Over a year ago

... though it's not clear to me what to do with the file pg_wal.tar

Laurenz Albe Over a year ago

It contains the transaction log files required to recover the database to a consistent state.

drmrbrewer Over a year ago

And needs to be copied into pg_wal/ folder when restoring? When pg_basebackup is run without the tar option, the content of pg_wal/ is OK, and can be restored without any further work?

Laurenz Albe Over a year ago

Yes and yes. Directory mode is simpler.

|

Collectives™ on Stack Overflow

Backup and restore postgresql data folder directly

1 Answer 1

6 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Related