Menu
Is there any way to dump a postgres db using psql only ( without pg_dump )? Thanks.
910
The only impact of pg_dump are the increased I/O load and the long running transaction it creates. The long transaction will keep autovacuum from reclaimimg dead tuples for the duration of the dump. Normally that is no big problem unless you have very high write activity in the database.
Note: pg_dump & pg_restore don't fully clear the entire database. DROP SCHEMA public CASCADE; CREATE SCHEMA public; If you are using PostgreSQL 9.3 or greater, you may also need to restore the default grants.
pg_dump creates a logical backup, that is a series of SQL statements that, when executed, create a new database that is logically like the original one. pg_basebackup creates a physical backup, that is a copy of the files that constitute the database cluster. You have to use recovery to make such a backup consistent.
pg_dump is a utility for backing up a PostgreSQL database. It makes consistent backups even if the database is being used concurrently. pg_dump does not block other users accessing the database (readers or writers).
Theoretically you have access to all the data needed. In practice you're more likely to be able to dump/save some data using COPY command, but not the database schema, etc.
Note, that you do not have to have pg_dump on the same machine where your database server is, if it listens to the network. But well, I don't know why you even ask :)
190
In theory you could run queries to extract the schema and then use those results to extract the data. But it wouldn't be easy to manipulate all of that into something usable for a restore using just psql.
34
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
