Thread: Restaring a dumped database
Hi Everyone,
I’ve used the following script for my dump; pg_dump -h 127.0.0.1 -p 5432 -U mohlomi -F p teleform > "teleform_Backup.dump" – and it worked fine.
After my dump, I dropped the database teleform - My nightmare starts when I try to restore and I get error massages that the database dropped already exist etc...
I used the script psql -h 127.0.0.1 -p 5432 -U mohlomi -d teleform < "teleform_Backup.dump" for restoring the database.
Funny enough the above scripts works at a table level!!!
Any assistance will be welcomed.
Thanks,
Mohlomi Moloi
Mohlomi Moloi wrote: > Hi Everyone, > > I’ve used the following script for my dump; pg_dump -h 127.0.0.1 -p 5432 > -U mohlomi -F p teleform > "teleform_Backup.dump" – and it worked fine. > > After my dump, I dropped the database teleform - My nightmare starts > when I try to restore and I get error massages that the database dropped > already exist etc... > > I used the script psql -h 127.0.0.1 -p 5432 -U mohlomi -d teleform < > "teleform_Backup.dump" for restoring the database. > > Funny enough the above scripts works at a table level!!! > > Any assistance will be welcomed. If you are trying to restore over an existing database; then you should either: 1) use -c option on pg_dump. 2) manually drop all tables, indexes and database.
Thanks for the help Frank!! However I'm now faced with a question - Why my database after the restore worked, all of a suddengrew bigger. Thanks, Mohlomi Moloi ----- Original Message ----- From: "Frank Bax" <fbax@sympatico.ca> Sent: Mon, 11/8/2010 2:19pm To: "PostgreSQL List - Novice" <pgsql-novice@postgresql.org> Subject: Re: [NOVICE] Restaring a dumped database Mohlomi Moloi wrote: > Hi Everyone, > > I’ve used the following script for my dump; pg_dump -h 127.0.0.1 -p 5432 > -U mohlomi -F p teleform > "teleform_Backup.dump" – and it worked fine. > > After my dump, I dropped the database teleform - My nightmare starts > when I try to restore and I get error massages that the database dropped > already exist etc... > > I used the script psql -h 127.0.0.1 -p 5432 -U mohlomi -d teleform < > "teleform_Backup.dump" for restoring the database. > > Funny enough the above scripts works at a table level!!! > > Any assistance will be welcomed. If you are trying to restore over an existing database; then you should either: 1) use -c option on pg_dump. 2) manually drop all tables, indexes and database. -- Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-novice
How did you measure "bigger"? Mohlomi Moloi wrote: > Thanks for the help Frank!! However I'm now faced with a question - Why my database after the restore worked, all of asudden grew bigger. > > Thanks, > > Mohlomi Moloi > > ----- Original Message ----- > From: "Frank Bax" <fbax@sympatico.ca> > Sent: Mon, 11/8/2010 2:19pm > To: "PostgreSQL List - Novice" <pgsql-novice@postgresql.org> > Subject: Re: [NOVICE] Restaring a dumped database > > Mohlomi Moloi wrote: >> Hi Everyone, >> >> I’ve used the following script for my dump; pg_dump -h 127.0.0.1 -p 5432 >> -U mohlomi -F p teleform > "teleform_Backup.dump" – and it worked fine. >> >> After my dump, I dropped the database teleform - My nightmare starts >> when I try to restore and I get error massages that the database dropped >> already exist etc... >> >> I used the script psql -h 127.0.0.1 -p 5432 -U mohlomi -d teleform < >> "teleform_Backup.dump" for restoring the database. >> >> Funny enough the above scripts works at a table level!!! >> >> Any assistance will be welcomed. > > > > If you are trying to restore over an existing database; then you should > either: > 1) use -c option on pg_dump. > 2) manually drop all tables, indexes and database. >
Bigger measured by looking at the previous size of the database - that was 34 megabytes and the restored was 800 megabytes.What I noticed was some of my table had triple the amount of rows in contrast to the old database. :) ----- Original Message ----- From: "Frank Bax" <fbax@sympatico.ca> Sent: Wed, 11/10/2010 2:41pm To: "PostgreSQL List - Novice" <pgsql-novice@postgresql.org> Subject: Re: [NOVICE] Restaring a dumped database How did you measure "bigger"? Mohlomi Moloi wrote: > Thanks for the help Frank!! However I'm now faced with a question - Why my database after the restore worked, all of asudden grew bigger. > > Thanks, > > Mohlomi Moloi > > ----- Original Message ----- > From: "Frank Bax" <fbax@sympatico.ca> > Sent: Mon, 11/8/2010 2:19pm > To: "PostgreSQL List - Novice" <pgsql-novice@postgresql.org> > Subject: Re: [NOVICE] Restaring a dumped database > > Mohlomi Moloi wrote: >> Hi Everyone, >> >> I’ve used the following script for my dump; pg_dump -h 127.0.0.1 -p 5432 >> -U mohlomi -F p teleform > "teleform_Backup.dump" – and it worked fine. >> >> After my dump, I dropped the database teleform - My nightmare starts >> when I try to restore and I get error massages that the database dropped >> already exist etc... >> >> I used the script psql -h 127.0.0.1 -p 5432 -U mohlomi -d teleform < >> "teleform_Backup.dump" for restoring the database. >> >> Funny enough the above scripts works at a table level!!! >> >> Any assistance will be welcomed. > > > > If you are trying to restore over an existing database; then you should > either: > 1) use -c option on pg_dump. > 2) manually drop all tables, indexes and database. > -- Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-novice
Mohlomi Moloi <mmoloi@khulisa.com> writes:
> Bigger measured by looking at the previous size of the database - that was 34 megabytes and the restored was 800
megabytes.What I noticed was some of my table had triple the amount of rows in contrast to the old database. :)
Maybe you did the restore three times? If you do that and ignore errors
about objects already existing, you could end up with multiple copies
of all rows (at least in tables without any unique indexes).
regards, tom lane
Tom Lane! Tom Lane! Tom Lane! Just checked my history to confirm and yes
I've ignored errors about objects already existing. Problem solved with
the cause known!
Best regards, Mohlomi Moloi
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: 10 November 2010 17:12
To: Mohlomi Moloi
Cc: Frank Bax; PostgreSQL List - Novice
Subject: Re: [NOVICE] Restaring a dumped database
Mohlomi Moloi <mmoloi@khulisa.com> writes:
> Bigger measured by looking at the previous size of the database - that
was 34 megabytes and the restored was 800 megabytes. What I noticed was
some of my table had triple the amount of rows in contrast to the old
database. :)
Maybe you did the restore three times? If you do that and ignore errors
about objects already existing, you could end up with multiple copies
of all rows (at least in tables without any unique indexes).
regards, tom lane