Thread: How to restore to empty database
Hi!
I want to restore to new empty database using pg_restore .
pg_restore should create new empty database and restore into it.
If database exists, pg_restore should clean all data from it or drop and
create new empty database before restore.
According to pg_restore doc, switches --clean --create --if-exists should
do this.
I tried
PG_COLOR=auto
PGHOST=localhost
PGPASSWORD=mypass
PGUSER=postgres
export PGHOST PGPASSWORD PG_COLOR PGUSER
pg_restore --clean --create --if-exists --dbname=mydb --jobs=4 --verbose
"mydb.backup"
but got error
pg_restore: connecting to database for restore
pg_restore: error: connection to database "mydb" failed: FATAL: database
"mydb" does not exist
I also tried without --dbname=mydb but then got error
pg_restore: error: one of -d/--dbname and -f/--file must be specified
How to restore to database which does not exist to drop existing database
before restore if it exists ?
Should I invoke
drop database if exists
and
create database
commands before calling pg_restore or can pg_restore do it itself ?
Using Postgres 12 on Debian 10
Andrus.
On 1/30/20 2:23 PM, Andrus wrote: > Hi! > > I want to restore to new empty database using pg_restore . > pg_restore should create new empty database and restore into it. > If database exists, pg_restore should clean all data from it or drop and > create new empty database before restore. > > According to pg_restore doc, switches --clean --create --if-exists > should do this. > > I tried > > PG_COLOR=auto > PGHOST=localhost > PGPASSWORD=mypass > PGUSER=postgres > export PGHOST PGPASSWORD PG_COLOR PGUSER > pg_restore --clean --create --if-exists --dbname=mydb --jobs=4 > --verbose "mydb.backup" > > but got error > > pg_restore: connecting to database for restore > pg_restore: error: connection to database "mydb" failed: FATAL: > database "mydb" does not exist > > I also tried without --dbname=mydb but then got error > > pg_restore: error: one of -d/--dbname and -f/--file must be specified You need to connect to a database that exists with --dbname, for instance --dbname=postgres. Postgres will then use that connection to create the new database, in your case mydb. > > How to restore to database which does not exist to drop existing > database before restore if it exists ? > Should I invoke > > drop database if exists > > and > > create database > > commands before calling pg_restore or can pg_restore do it itself ? > > Using Postgres 12 on Debian 10 > > Andrus. > > -- Adrian Klaver adrian.klaver@aklaver.com
Hi! >You need to connect to a database that exists with --dbname, for >instance --dbname=postgres. Postgres will then use that connection to >create the new database, in your case mydb. Thank you, this seems work. There are total 24 databases, .backup files total size in 37GB , aprox 60 % from this from bytea columns ( pdf documents, images). Using VPS server, 4 cores, 11 GB RAM, used only for postgres. Which is the fastest way to restore data from all of them to empty databases. Should I run all commands in sequence like pg_restore --clean --create --if-exists --verbose --dbname=postgres --jobs=4 "database1.backup" pg_restore --clean --create --if-exists --verbose --dbname=postgres --jobs=4 "database2.backup" ... pg_restore --clean --create --if-exists --verbose --dbname=postgres --jobs=4 "database24.backup" or run them all parallel without --jobs=4 like pg_restore --clean --create --if-exists --verbose --dbname=postgres "database1.backup" & pg_restore --clean --create --if-exists --verbose --dbname=postgres "database2.backup" & ... pg_restore --clean --create --if-exists --verbose --dbname=postgres --jobs=4 "database24.backup" & or some balance between those ? Is there some postgres or Debian setting which can used during restore time to speed up restore ? I use shared_buffers=1GB , other settings from debian installation. Andrus.
On 1/30/20 3:08 PM, Andrus wrote: > Hi! > >> You need to connect to a database that exists with --dbname, for >> instance --dbname=postgres. Postgres will then use that connection to >> create the new database, in your case mydb. > > Thank you, this seems work. > > There are total 24 databases, .backup files total size in 37GB , aprox 60 % > from this from bytea columns ( pdf documents, images). > Using VPS server, 4 cores, 11 GB RAM, used only for postgres. > Which is the fastest way to restore data from all of them to empty > databases. Should I run all commands in sequence like These days 37 GB is relatively small, so you maybe getting into the realm of premature optimization. Do the 24 databases represent an entire cluster you are trying to transfer? If so have you looked at pg_dumpall?: https://www.postgresql.org/docs/12/app-pg-dumpall.html It is a text based backup, but it will include all the databases and the globals. Otherwise I think you are going to find that the below will not make much difference given the overall size of the backup files. > > pg_restore --clean --create --if-exists --verbose --dbname=postgres > --jobs=4 > "database1.backup" > pg_restore --clean --create --if-exists --verbose --dbname=postgres > --jobs=4 > "database2.backup" > ... > pg_restore --clean --create --if-exists --verbose --dbname=postgres > --jobs=4 > "database24.backup" > > or run them all parallel without --jobs=4 like > > pg_restore --clean --create --if-exists --verbose --dbname=postgres > "database1.backup" & > pg_restore --clean --create --if-exists --verbose --dbname=postgres > "database2.backup" & > ... > pg_restore --clean --create --if-exists --verbose --dbname=postgres > --jobs=4 > "database24.backup" & > > > or some balance between those ? > Is there some postgres or Debian setting which can used during restore time > to speed up restore ? > I use shared_buffers=1GB , other settings from debian installation. > > Andrus. -- Adrian Klaver adrian.klaver@aklaver.com
Hi! >These days 37 GB is relatively small, so you maybe getting into the realm >of premature optimization. Do the 24 databases represent an entire cluster >you are trying to transfer? Basically yes. Cluster contains also small test database which actually does not need transferred but I can delete it manually after transfer. Also postgres, template0 and template1 are not used directly by applications and probably does not need to be transferred. >If so have you looked at pg_dumpall?: >https://www.postgresql.org/docs/12/app-pg-dumpall.html >It is a text based backup, but it will include all the databases and the >globals. Source cluster is in old Debian 6 Squeeze running Postgres 9.1 Should I create pipe using pg_dumpall and restore everything from old using pipe instead of pg_dump/pg_restore ? Andrus.
On 1/30/20 10:21 PM, Andrus wrote: > Hi! > >> These days 37 GB is relatively small, so you maybe getting into the >> realm of premature optimization. Do the 24 databases represent an >> entire cluster you are trying to transfer? > > Basically yes. > Cluster contains also small test database which actually does not need > transferred but I can delete it manually after transfer. > Also postgres, template0 and template1 are not used directly by > applications and probably does not need to be transferred. They are created with a new cluster anyway. That being said you may want to recreate them if you have installed objects in them. For that see the --clean option to pg_dumpall. If you do use that make sure and fully read the Notes and Examples here: https://www.postgresql.org/docs/11/app-pg-dumpall.html > >> If so have you looked at pg_dumpall?: >> https://www.postgresql.org/docs/12/app-pg-dumpall.html >> It is a text based backup, but it will include all the databases and >> the globals. > > Source cluster is in old Debian 6 Squeeze running Postgres 9.1 > Should I create pipe using pg_dumpall and restore everything from old > using pipe instead of pg_dump/pg_restore ? That would depend on how stable and fast the connection is between the two machines. What sort of time frame are you working with? > > Andrus. > -- Adrian Klaver adrian.klaver@aklaver.com