Fwd: [GENERAL] Restore problem from 8.4 backup to 9.0
If I run the admin tool from my postgres account, that works fine since postgres is the owner. > Cc: pgsql-general@postgresql.org > Subject: Re: Fwd: [GENERAL] Restore problem from 8.4 backup to 9.0 > >> Thanks for the fast reply. I must have still been connected to the older >> database somehow. I cleaned up my installation and restored the database. >> No error messages this time. I need to change the pg_hba.conf file. I read >> the documentation and its supposed to be in the data directory (which is >> locked), but it doesn't seem to be there. Is there an easy way to create >> the file in the data directory (without overriding access privileges) at >> this point? > > That's where it would normally be, but try "show hba_file;" if you think > the packager of your distribution put it somewhere else. > > regards, tom lane > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
Fwd: [GENERAL] Restore problem from 8.4 backup to 9.0
Where would I type 'show hba_file'? I'm on Mac. Also I downloaded the distribution from postgresql.org. If I run an initdb and I already restored a database, will it put the pg_hba.conf file in the 'data' folder? > Subject: Re: Fwd: [GENERAL] Restore problem from 8.4 backup to 9.0 > > Adam Wizon writes: >> Thanks for the fast reply. I must have still been connected to the older >> database somehow. I cleaned up my installation and restored the database. >> No error messages this time. I need to change the pg_hba.conf file. I read >> the documentation and its supposed to be in the data directory (which is >> locked), but it doesn't seem to be there. Is there an easy way to create >> the file in the data directory (without overriding access privileges) at >> this point? > > That's where it would normally be, but try "show hba_file;" if you think > the packager of your distribution put it somewhere else. > > regards, tom lane > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
Re: Fwd: [GENERAL] Restore problem from 8.4 backup to 9.0
Adam Wizon writes: > Thanks for the fast reply. I must have still been connected to the older > database somehow. I cleaned up my installation and restored the database. > No error messages this time. I need to change the pg_hba.conf file. I read > the documentation and its supposed to be in the data directory (which is > locked), but it doesn't seem to be there. Is there an easy way to create the > file in the data directory (without overriding access privileges) at this > point? That's where it would normally be, but try "show hba_file;" if you think the packager of your distribution put it somewhere else. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Fwd: [GENERAL] Restore problem from 8.4 backup to 9.0
Thanks for the fast reply. I must have still been connected to the older database somehow. I cleaned up my installation and restored the database. No error messages this time. I need to change the pg_hba.conf file. I read the documentation and its supposed to be in the data directory (which is locked), but it doesn't seem to be there. Is there an easy way to create the file in the data directory (without overriding access privileges) at this point? Begin forwarded message: > From: Tom Lane > Date: September 28, 2010 9:58:10 PM EDT > To: Adam Wizon > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Restore problem from 8.4 backup to 9.0 > > Adam Wizon writes: >> I installed v9.0 on my Mac Pro. Dumped the 8.4 database using 'pg_dump -Fc >> -d dbname --username=xyz > backup_file_name' using the pg_dump from the 8.4 >> installation. I restored the database using 'pg_restore -d dbname >> backup_file_name' using the 9.0 restore and after creating a new database >> under 9.0. Under version 9.0 the database looks ok, but I had a lot of the >> following errors (132) during the restore: >> pg_restore: [archiver (db)] could not execute query: ERROR: relation "xyz" >> already exists ... >> pg_restore: [archiver (db)] could not execute query: ERROR: multiple >> primary keys for >> pg_restore: [archiver (db)] COPY failed: ERROR: duplicate key value >> violates unique constraint ... > >> I tried restoring the 8.4 restore file to a new 8.4 database and there >> were no errors. It almost looks like the schema already existed when I >> went to do the restore, but I was careful to create the new database in >> the admin tool under the correct server. > > Restoring twice is almost certainly the explanation. > > regards, tom lane > > -- > 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] Restore problem from 8.4 backup to 9.0
Adam Wizon writes: > I installed v9.0 on my Mac Pro. Dumped the 8.4 database using 'pg_dump -Fc > -d dbname --username=xyz > backup_file_name' using the pg_dump from the 8.4 > installation. I restored the database using 'pg_restore -d dbname > backup_file_name' using the 9.0 restore and after creating a new database > under 9.0. Under version 9.0 the database looks ok, but I had a lot of the > following errors (132) during the restore: > pg_restore: [archiver (db)] could not execute query: ERROR: relation "xyz" > already exists ... > pg_restore: [archiver (db)] could not execute query: ERROR: multiple primary > keys for > pg_restore: [archiver (db)] COPY failed: ERROR: duplicate key value violates > unique constraint ... > I tried restoring the 8.4 restore file to a new 8.4 database and there > were no errors. It almost looks like the schema already existed when I > went to do the restore, but I was careful to create the new database in > the admin tool under the correct server. Restoring twice is almost certainly the explanation. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Restore problem from 8.4 backup to 9.0
I installed v9.0 on my Mac Pro. Dumped the 8.4 database using 'pg_dump -Fc -d dbname --username=xyz > backup_file_name' using the pg_dump from the 8.4 installation. I restored the database using 'pg_restore -d dbname backup_file_name' using the 9.0 restore and after creating a new database under 9.0. Under version 9.0 the database looks ok, but I had a lot of the following errors (132) during the restore: pg_restore: [archiver (db)] could not execute query: ERROR: relation "xyz" already exists ... pg_restore: [archiver (db)] could not execute query: ERROR: multiple primary keys for pg_restore: [archiver (db)] COPY failed: ERROR: duplicate key value violates unique constraint ... I tried restoring the 8.4 restore file to a new 8.4 database and there were no errors. It almost looks like the schema already existed when I went to do the restore, but I was careful to create the new database in the admin tool under the correct server. I thought the admin tool created the new database in the data folder of the selected installation. Any ideas on what might have happened? Thanks. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general