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

tanks for reply.

Why should I connect to a existing database to restore another one ?
from documentation,pg_restore seems to can do this :
http://www.postgresql.org/docs/8.0/interactive/app-pgrestore.html

-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
@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
.............................

________________________________
From: Richard Huxton <dev@archonet.com>
To: Mr. John <mr_johnmr@yahoo.com>
Cc: pgsql-general@postgresql.org
Sent: Monday, November 17, 2008 11:31:28 AM
Subject: Re: [GENERAL] 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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

thanks for answer Richard
I'm new with Postgre,my version is 8.3
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
How exactly do I have to run pg_restore to create my database and then restore it as in your suggestion no.2
(2. Get pg_restore to create the database for you then switch to it,
while connecting with "-d" to an existing database.)

Thanks.

________________________________
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

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
------------------------------------------------------------------

Thanks Raymond
"pg_restore.exe" -h 192.168.1.1 -p 5432 -U postgres -d "postgres" -C "stru.tar" 2>>log_resto.txt
echo %errorlevel% >err_resto.txt

created and restored my database with the name saved in backup
Thanks a lot.

________________________________
From: Raymond O'Donnell <rod@iol.ie>
To: Mr. John <mr_johnmr@yahoo.com>
Cc: pgsql-general@postgresql.org
Sent: Monday, November 17, 2008 1:49:17 PM
Subject: Re: [GENERAL] Problem with pg_restore into new database

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
------------------------------------------------------------------

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general