I had the data center linux experts upgrade PostGreSQL on our box from 8.1 (very old!) to 9.0 In particular I really needed the new ENUM data type.
But now I'm facing issues restoring the databases. First the data team forgot to restore pg_hba.conf... after I did that at least WebMin was able to log in to the psql server.
But now when I go to restore data bases from the command line
%psql -d postgres -f /home/dbase-backups/hap_users.sql
I am first hit with "role 'himalayan' does not exist
so I go to Web min and add a user 'himalayan"
so then I try to run the command again and I seem to have anamolies in the original data base which were over looked by Pgsql 8.1 but are unacceptable now in 9.0
psql:/home/dbase-backups/hap_users.sql:363: ERROR: duplicate key value violates unique constraint "donations_pkey" DETAIL: Key (donation_id)=(21) already exists. CONTEXT: COPY donations, line 1: "21 f Aran and Valli Smith \N 6242 streeet address city HI 96746 United States 2010-10-02 16:37:31-07..." psql:/home/dbase-backups/hap_users.sql:371: ERROR: multiple primary keys for table "donations" are not allowed REVOKE etc...
so, now I'm stuck and out of my depth.
I think this may not be the forum to go to for help unless we have some experience PostGreSQL jockeys here who can help me ride this to the finish line.
Any guidance will be helpful.. I'm of to the PostGreSQL user forums next.
More on this: still in trouble... I need to know
When you do a back up of a PostGreSQL database from inside webMin... does it issue a pg_dump or a pg_dumpall to the .sql file?
Howdy,
I'm unfortunately not overly familiar with Postgres to assist with your initial question... however, I can offer that Virtualmin does appear to use pg_dump to generate Postgres backups, and pg_restore to do the restores.
I hope that helps a little bit at least :-)
-Eric