What I’m trying to do:
We have an anvil-app-server running locally together with the default Postgres DB.
We would like to perform regular updates of the entire DB without interfering with the running system.
The database has ~10k entries of mainly text (no binary data). What is the recommended way of creating local backups for DBs of that size?
Ideally we would like to serialize the entire DB into a single file using pg_dump or something similar.
Since you have a local installation of PosgreSQL I would just use whatever you like to back up an SQL database of that type, either through the cli with pg_dump like you already stated, or using something more managed, like
You can also check this out:
Happily, the answer is “pg_dump will work just fine”. Thanks to Postgres’s snapshot isolation, a pg_dump operation doesn’t pause the rest of the database!
Obligatory plug: The Enterprise App Server supports built-in streaming backups to filesystem or cloud storage, and can restore itself to any point in time with one command – all out-of-the-box with no extra setup. If you’re running the App Server in a commercial setting and would benefit from a fully supported option with extra bells and whistles, drop us a line at sales@anvil.works!
thank you for your replies. We are now using pg_dump.
Here are the commands we use in case someone else needs it:
Create a backup:
pg_dump -p [port] -U postgres -d postgres -h localhost -F t -f backup.tar
Restore from backup:
pg_restore -U postgres -h localhost -p [port] -F t -d postgres --clean backup.tar
Hi mrh,
we want to build a server function to run that:
‘’‘import subprocess
subprocess.run([‘pg_dump’, f"–dbname=’{connection_string}‘", ‘|’, ‘gzip’, ‘>’, backup_file_name], shell=True, check=True)’‘’
But pg_dump can not be found. How do we have to change that?
what is the path ‘/path/somewhere/pg_dump’