When you use
pg_restore you have various options available, for example :
-c to drop
database objects before recreating them,
-e exit if
an error has encountered,
-F format to
specify the format of the archive.
I tried to
replicate this. I created an object in database nexaprod which is running on
port 5432.
nexaprod =# CREATE TABLE tutorials (id int, tutorial_name text); CREATE TABLE
nexaprod =# \d List of relations Schema | Name | Type | Owner --------+-----------+-------+---------- public | tutorials | table | postgres (1 row)
Now I wish
to restore this database to another cluster of port 5433 as below:
-bash-4.1$ /usr/pgsql-9.6/bin/pg_restore -d nexaprod /var/lib/pgsql/newdb1.tar -C -p 5433 pg_restore: [archiver (db)] connection to database " nexaprod" failed: FATAL: database " nexaprod" does not existt
What I've
got in file wrote above 'create database' so this database does not exist when I’m
running script.
That's what
for there is always 'postgres' database. So, I tried something as this :
-bash-4.1$ /usr/pgsql-9.6/bin/pg_restore -C -d postgres -v -p 5433 /var/lib/pgsql/nexaprod.tar
pg_restore: connecting to database for restore pg_restore: creating DATABASE "nexaprod" pg_restore: connecting to new database "nexaprod" pg_restore: connecting to database "nexaprod" as user "postgres" pg_restore: creating SCHEMA "public" pg_restore: creating COMMENT "SCHEMA public" pg_restore: creating EXTENSION "plpgsql" pg_restore: creating COMMENT "EXTENSION plpgsql" pg_restore: creating TABLE "public.tutorials" pg_restore: processing data for table "public.tutorials" pg_restore: creating ACL "SCHEMA public" pg_restore: creating ACL "public.TABLE tutorials"
-bash-4.1$ psql -p 5433
The above
states that :
-
pg_restore will initially
connect to the database specified with -d. It will NOT create that database.
- The -d will restore in the given database if and only if -C is not
used.
- It creates a database with the name from the archive you are
restoring and restores the data into that database, when used -C.
So,
basically what above did is :
1. connect
to postgres database
2. Create
database -nexaprod
3.
Disconnect from postgres and connect to -nexaprod
4. Upload
data into database -nexaprod
Alternatively, we can use below command to restore the dump with different DB name.
$ cat /var/lib/pgsql/nexaprod.tar | psql nexa_dev -p 5433
postgres=# create database nexa_dev ; CREATE DATABASE postgres=# \q
-bash-4.1$ cat /var/lib/pgsql/nexaprod.tar | psql nexa_dev -p 5433 postgres=# \l List of databases Name | Owner | Encoding | Collation | Ctype | Access privileges -----------+----------+----------+-------------+-------------+----------------------- nexa_dev | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | nexaprod | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | postgres=# \c nexa_dev You are now connected to database "nexa_dev". nexa_dev=# \d List of relations Schema | Name | Type | Owner --------+-----------+-------+---------- public | tutorials | table | postgres (1 row)
No comments:
Post a Comment