[GENERAL] Problem with pg_restore into new database
Hi. I use latest version of Postgre in windows XP I try to restore a database from .tar backup,into a new database.Using EMS Manager it works fine,my database is creating and restoring succesfully. My application creates a .bat file and run it,restore is unsuccesfully but file is : @echo off pg_restore.exe -h 192.168.1.1 -p 5432 -U postgres -C -v stru.tar 2log_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 pg_restore: creating TABLE SALI pg_restore: creating TABLE comenzi pg_restore: creating TABLE comenzi_desf pg_restore: creating TABLE mese pg_restore: creating TABLE operatori pg_restore: creating TABLE societati pg_restore: creating SEQUENCE comenzi_desf_idcomanda_seq pg_restore: executing SEQUENCE SET comenzi_desf_idcomanda_seq pg_restore: creating SEQUENCE comenzi_desf_poz_seq pg_restore: creating SEQUENCE OWNED BY comenzi_desf_poz_seq pg_restore: executing SEQUENCE SET comenzi_desf_poz_seq pg_restore: creating SEQUENCE comenzi_id_masa_seq pg_restore: executing SEQUENCE SET comenzi_id_masa_seq pg_restore: creating SEQUENCE comenzi_id_sala_seq pg_restore: executing SEQUENCE SET comenzi_id_sala_seq pg_restore: creating SEQUENCE comenzi_id_seq pg_restore: creating SEQUENCE OWNED BY comenzi_id_seq pg_restore: executing SEQUENCE SET comenzi_id_seq pg_restore: creating SEQUENCE mese_id_sali_seq pg_restore: executing SEQUENCE SET mese_id_sali_seq pg_restore: creating SEQUENCE mese_id_seq pg_restore: executing SEQUENCE SET mese_id_seq pg_restore: creating SEQUENCE operatori_id_seq pg_restore: executing SEQUENCE SET operatori_id_seq pg_restore: creating SEQUENCE sali_id_seq pg_restore: executing SEQUENCE SET sali_id_seq pg_restore: creating DEFAULT id pg_restore: creating DEFAULT poz pg_restore: restoring data for table SALI pg_restore: restoring data for table comenzi pg_restore: restoring data for table comenzi_desf pg_restore: restoring data for table mese pg_restore: restoring data for table operatori pg_restore: restoring data for table societati pg_restore: creating CONSTRAINT SALI_pkey pg_restore: creating CONSTRAINT comenzi_desf_IdComanda_key pg_restore: creating CONSTRAINT comenzi_desf_pkey pg_restore: creating CONSTRAINT comenzi_id_masa_key pg_restore: creating CONSTRAINT comenzi_id_sala_key pg_restore: creating CONSTRAINT comenzi_pkey pg_restore: creating CONSTRAINT mese_id_Sali_key pg_restore: creating CONSTRAINT mese_pkey pg_restore: creating CONSTRAINT operatori_pkey pg_restore: creating CONSTRAINT societati_pkey pg_restore: creating FK CONSTRAINT comenzi_desf_fk pg_restore: creating FK CONSTRAINT comenzi_fk pg_restore: creating FK CONSTRAINT comenzi_fk1 pg_restore: creating FK CONSTRAINT comenzi_fk2 pg_restore: creating FK CONSTRAINT mese_fk pg_restore: setting owner and privileges for DATABASE _struct_fis_ pg_restore: setting owner and privileges for SCHEMA pos pg_restore: setting owner and privileges for SCHEMA public pg_restore: setting owner and privileges for COMMENT SCHEMA public pg_restore: setting owner and privileges for ACL public pg_restore: setting owner and privileges for PROCEDURAL LANGUAGE plpgsql pg_restore: setting owner and privileges for TABLE SALI pg_restore: setting owner and privileges for TABLE comenzi pg_restore: setting owner and privileges for TABLE comenzi_desf pg_restore: setting owner and privileges for TABLE mese pg_restore: setting owner and privileges for TABLE operatori pg_restore: setting owner and privileges for TABLE societati pg_restore: setting owner and privileges for SEQUENCE comenzi_desf_idcomanda_seq pg_restore: setting owner and privileges for SEQUENCE comenzi_desf_poz_seq pg_restore: setting owner and privileges for SEQUENCE OWNED BY comenzi_desf_poz_seq pg_restore: setting owner and privileges for SEQUENCE comenzi_id_masa_seq pg_restore: setting owner and privileges for SEQUENCE comenzi_id_sala_seq pg_restore: setting owner and privileges for SEQUENCE comenzi_id_seq pg_restore: setting owner and privileges for SEQUENCE OWNED BY comenzi_id_seq pg_restore: setting owner and privileges for SEQUENCE mese_id_sali_seq pg_restore: setting owner and privileges for SEQUENCE mese_id_seq pg_restore: setting owner and privileges for SEQUENCE operatori_id_seq pg_restore: setting owner and privileges for SEQUENCE sali_id_seq pg_restore: setting owner and privileges for DEFAULT id pg_restore: setting owner and privileges for DEFAULT poz pg_restore: setting owner and privileges for CONSTRAINT SALI_pkey pg_restore: setting owner and privileges for CONSTRAINT comenzi_desf_IdComanda_key pg_restore: setting owner and privileges for CONSTRAINT comenzi_desf_pkey pg_restore: setting owner and privileges for CONSTRAINT comenzi_id_masa_key pg_restore: setting owner and privileges for CONSTRAINT
Re: [GENERAL] Problem with pg_restore into new database
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 2log_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 [EMAIL PROTECTED] To: Mr. John [EMAIL PROTECTED] 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 2log_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
Re: [GENERAL] Problem with pg_restore into new database
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 [EMAIL PROTECTED] To: Mr. John [EMAIL PROTECTED] 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 2log_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
Re: [GENERAL] Problem with pg_restore into new database
Thanks Raymond pg_restore.exe -h 192.168.1.1 -p 5432 -U postgres -d postgres -C stru.tar 2log_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 [EMAIL PROTECTED] To: Mr. John [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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
