Обсуждение: Problem with pg_restore into new database

Mr. John wrote:
> but on the server is no database called _struct_fis_
> err_resto.txt file contents only "0".
>
> If I add     -d
> @echo off
>  "pg_restore.exe"  -h 192.168.1.1 -p 5432 -U postgres -d "_struct_fis_" -C -v "stru.tar"   2>>log_resto.txt
>  echo %errorlevel% >err_resto.txt
>
> error is "1" and log file  :
>
>    pg_restore: connecting to database for restore
> pg_restore: [archiver (db)] connection to database "_struct_fis_" failed: FATAL:  database "_struct_fis_" does not
exist
> pg_restore: *** aborted because of error

I'm not sure I understand your problem fully, but this is because you
are trying to connect (-d ...) to database _struct_fis_ before it's
created. Connect to a database you know exists (template1 or postgres
for example) and then let the restore create the database.


--
  Richard Huxton
  Archonet Ltd

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

On 17/11/2008 11:26, Mr. John wrote:
> I read pg_restore documentation and what I understood is that  using
> pg_restore.exe with -C will create  the database named in backup archive
> or specified with -d

You have to connect to *some* database, as Postgres doesn't have the
concept of just connecting to the server without connecting to a database.

Postgres has a maintenance database called "postgres" which is empty,
and it's normal to use this for initial connections if you haven't
already got a specific database to work with. In psql, there's a \c
command with changes the database to which you're connected, and you'll
see this command used in the output from pg_dump to connect to the
database being dumped.

HTH,

Ray.


------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------