Best way to make PostgreSQL backups

Question

I have a site that uses PostgreSQL. All content that I provide in my site is created at a development environment (this happens because it's webcrawler content). The only information created at the production environment is information about the users.

I need to find a good way to update data stored at production. May I restore to production only the tables updated at development environment and PostgreSQL will update this records at production or the best way would be to backup the users information at production, insert them at development and restore the whole database at production?

Thank you

Do you need users data updated at dev environment? – Michel Milezzi Nov 13 '17 at 18:21 — Michel Milezzi, Nov 13 '17 at 18:21

score 2 · Answer 1 · answered Nov 13 '17 at 18:24

You can use pg_dump to export the data just from the non-user tables in the development environment and pg_restore to bring that into prod.

The -t switch will let you pick specific tables.

pg_dump -d <database_name> -t <table_name>

https://www.postgresql.org/docs/current/static/app-pgdump.html

Michel Milezzi · Answer 2 · 2017-11-13T18:59:59.670

There are many tips arounds this subject here and here. I'd suggest you to take a look on these links before everything.

If your data is discarded at each update process then a plain dump will be enough. You can redirect pg_dump output directly to psql connected on production to avoid pg_restore step, something like below:

#Of course you must drop tables to load it again
#so it'll be reasonable to make a full backup before this
pg_dump -Fp -U user -h host_to_dev -T=user your_db | psql -U user -h host_to_production your_db

You might asking yourself "Why he's saying to drop my tables"?

Bulk loading data on a fresh table is faster than deleting old data and inserting again. A quote from the docs:

Creating an index on pre-existing data is quicker than updating it incrementally as each row is loaded.

Ps¹: If you can't connect on both environment at same time then you need to do pg_restore manually.

Ps²: I don't recommend it but you can append --clean option on pg_dump to generate DROP statements automatically. Be extreme careful with this option to avoid dropping unnexpected objects.

Best way to make PostgreSQL backups

2 Answers2