CODEWITHSUNDEEP
postgresqlpg-restore
Tim
Tim

Reputation: 99428

how can I import a database by running just one command?

I try to learn how to import a database into PostgreSQL.

My attempt failed, where I use pg_restore -C ... -d ... to combine creation of a new database and importing from the file to the new database in one command:

$ sudo -u postgres pg_restore -C -d postgres dvdrental.tar 
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2194; 1262 17355 DATABASE pagila postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  invalid locale name: "English_United States.1252"
    Command was: CREATE DATABASE pagila WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'English_United States.1252' LC_CTYPE = 'English_United States.1252';            
pg_restore: [archiver (db)] could not execute query: ERROR:  database "pagila" does not exist
    Command was: ALTER DATABASE pagila OWNER TO postgres;        
pg_restore: [archiver (db)] could not reconnect to database: FATAL:  database "pagila" does not exist

My questions are:

The document says:

-C --create

Create the database before restoring into it. If --clean is also specified, drop and recreate the target database before connecting to it.

and even provide an example:

Assume we have dumped a database called mydb into a custom-format dump file:

$ pg_dump -Fc mydb > db.dump

To drop the database and recreate it from the dump:

$ dropdb mydb
$ pg_restore -C -d postgres db.dump

The database named in the -d switch can be any database existing in the cluster; pg_restore only uses it to issue the CREATE DATABASE command for mydb . With -C , data is always restored into the database name that appears in the dump file.

Thanks.

Update:

Thanks again. I found the binary file toc.dat in the dump contains the 

CREATE DATABASE pagila WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'English_United States.1252' LC_CTYPE = 'English_United States.1252';`

Is it possible modify it to make sudo -u postgres pg_restore -C -d postgres dvdrental.tar work?

Note that I have a working soluton:

Separate creation of a new database in psql and importing from the file to the new database:

template1=# CREATE DATABASE dvdrental;
CREATE DATABASE

$ sudo -u postgres pg_restore -d dvdrental dvdrental.tar
$

Upvotes: 3

Views: 1400

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246653

Your import failed because the operating system where you are trying to import the dump does not know the locale English_United States.1252. Either the operating system is not Windows, or the Windows doesn't have that locale installed.

pg_restore -C tries to create the database in the same way as it was on the original system, which is not possible. Explicitly creating the database works because it uses a locale that exists.

There is no way to make that CREATE DATABASE command succeed except to have the locale present, so if that is not possible, you have to run two commands to restore the dump.

Upvotes: 5

Related Questions