Re: Problem with pg_restore into new database

---

From: Richard Huxton <dev@archonet.com>
To: Mr. John <mr_johnmr@yahoo.com>
Cc: pgsql-general@postgresql.org
Sent: Monday, November 17, 2008 12:08:00 PM
Subject: Re: [GENERAL] Problem with pg_restore into new database

Mr. John wrote:
> tanks for reply.
>
> Why should I connect to a existing database to restore another one ?

How do you issue a "CREATE DATABASE" command without being connected to
something?

> from documentation,pg_restore seems to can do this :
> http://www.postgresql.org/docs/8.0/interactive/app-pgrestore.html

Ideally, you don't want to be running 8.0 on Windows - it was the first
release on that platform and you'll find a lot of improvements if you
can set aside the time to upgrade to a more recent version. If you are
running 8.0, make sure it's 8.0.19 to get all the latest bug fixes.

> -C
> --create
> Create the database before restoring into it. (When this option is
> used, the database named with -d is used only to issue the initial
> CREATE DATABASE command. All data is restored into the database name
> that appears in the archive.)

> in my first attempt was no -d,no error and
> ... NO database

If you're not connecting to a database, where do you think the output
goes? Hint - it can't be the database server since you're not connected
to a database.

> @echo off
>  "pg_restore.exe"  -h 192.168.1.1 -p 5432 -U postgres  -C -v "stru.tar"  2>>log_resto.txt
>  echo %errorlevel% >err_resto.txt
>
> log file is :
> pg_restore: creating DATABASE _struct_fis_
> pg_restore: connecting to new database "_struct_fis_"
> pg_restore: creating SCHEMA pos
> pg_restore: creating SCHEMA public
> pg_restore: creating COMMENT SCHEMA public
> pg_restore: creating PROCEDURAL LANGUAGE plpgsql

At a guess, you can't see STDOUT while you're doing this, can you?

Your options are:

1. Create the database and restore directly into it with "-d"
2. Get pg_restore to create the database for you then switch to it,
while connecting with "-d" to an existing database.
3. Have pg_restore output to STDOUT and pipe that to psql with whatever
connection settings you want (which is just duplicating option 2).

--
  Richard Huxton
  Archonet Ltd