Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Dumping a DB without pg_dump

Is there any way to dump a postgres db using psql only ( without pg_dump )? Thanks.

like image 910
pgpv Avatar asked Feb 24 '12 15:02

pgpv


People also ask

Does pg_dump affect performance?

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.

Does pg_dump delete 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.

What is the difference between pg_dump and Pg_basebackup?

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.

Does pg_dump lock database?

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).


2 Answers

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 :)

like image 190
Michael Krelin - hacker Avatar answered Sep 21 '22 16:09

Michael Krelin - hacker


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.

like image 34
PinnyM Avatar answered Sep 20 '22 16:09

PinnyM