pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedrestore a table in a database
3+ messages / 2 participants
[nested] [flat]
* restore a table in a database
@ 2008-12-03 19:21 Marc Fromm <Marc.Fromm@wwu.edu>
2008-12-04 13:56 ` Re: restore a table in a database val <valiouk@yahoo.co.uk>
0 siblings, 1 reply; 3+ messages in thread
From: Marc Fromm @ 2008-12-03 19:21 UTC (permalink / raw)
To: pgsql-admin
How do I restore just a table to a database? I was able to create a backup of the required table from a backup of the database as follows.
I don't know how to properly restore the backed up table "departments" to the original "sms" database.
create a new db
createdb -U postgres smstest
restore a backup of the db to the new db
psql -U postgres smstest < /tmp/postgresql-sms-11-24-2008_04-05-database
backup up the specific table from the restored db
pg_dump -U postgres -a -t departments smstest > /tmp/smsdepartments
The table backup is smsdepartments. How do I restore smsdepartments to the table called departments in the sms database?
Thanks
Marc
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: restore a table in a database
2008-12-03 19:21 restore a table in a database Marc Fromm <Marc.Fromm@wwu.edu>
@ 2008-12-04 13:56 ` val <valiouk@yahoo.co.uk>
2008-12-04 19:18 ` Re: restore a table in a database Marc Fromm <Marc.Fromm@wwu.edu>
0 siblings, 1 reply; 3+ messages in thread
From: val @ 2008-12-04 13:56 UTC (permalink / raw)
To: pgsql-admin; Marc Fromm <Marc.Fromm@wwu.edu>
--- On Wed, 3/12/08, Marc Fromm <Marc.Fromm@wwu.edu> wrote:
> From: Marc Fromm <Marc.Fromm@wwu.edu>
> Subject: [ADMIN] restore a table in a database
> To: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
> Date: Wednesday, 3 December, 2008, 7:21 PM
> How do I restore just a table to a database? I was able to
> create a backup of the required table from a backup of the
> database as follows.
> I don't know how to properly restore the backed up
> table "departments" to the original
> "sms" database.
>
> create a new db
> createdb -U postgres smstest
>
> restore a backup of the db to the new db
> psql -U postgres smstest <
> /tmp/postgresql-sms-11-24-2008_04-05-database
>
> backup up the specific table from the restored db
> pg_dump -U postgres -a -t departments smstest >
> /tmp/smsdepartments
>
> The table backup is smsdepartments. How do I restore
> smsdepartments to the table called departments in the sms
> database?
>
> Thanks
>
> Marc
Open up the backup file and change the tablename in the CREATE and COPY commands from smsdepartments to departments. Then restore with:
psql -U postgres smstest < /tmp/smsdepartments
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: restore a table in a database
2008-12-03 19:21 restore a table in a database Marc Fromm <Marc.Fromm@wwu.edu>
2008-12-04 13:56 ` Re: restore a table in a database val <valiouk@yahoo.co.uk>
@ 2008-12-04 19:18 ` Marc Fromm <Marc.Fromm@wwu.edu>
0 siblings, 0 replies; 3+ messages in thread
From: Marc Fromm @ 2008-12-04 19:18 UTC (permalink / raw)
To: valiouk@yahoo.co.uk <valiouk@yahoo.co.uk>; +Cc: pgsql-admin
> change the tablename in the CREATE and COPY commands from smsdepartments to departments
My backed up table called smsdepartments was created by the command below from the database smstest, which is a backup of the database sms. The backup file smsdepartments (a backup of the departments table extracted from database smstest) only has a single COPY command (no CREATE command) and the table name is called departments.
pg_dump -U postgres -a -t departments smstest > /tmp/smsdepartments
> Then restore with: psql -U postgres smstest < /tmp/smsdepartments
The database smstest is a backup of the database sms. I need to restore the table called departments in database sms with the departments table in the database smstest.
I did this to restore the departments table to the sms database from the extracted backup table called smsdepartments from the smstest backup database and it appeared to have worked. I made no changes to the smsdepartments backup table file.
'psql -U postgres sms < /tmp/smsdepartments'
My uncertainty stems from googling how to restore a table to a database, and each article I read mentioned doing extensive edits to the backup of the table file, without actually explaining what edits to make. I made no edits and it appears the data is all restored that was missing from the departments table.
-----Original Message-----
From: val [mailto:valiouk@yahoo.co.uk]
Sent: Thursday, December 04, 2008 5:57 AM
To: pgsql-admin@postgresql.org; Marc Fromm
Subject: Re: [ADMIN] restore a table in a database
--- On Wed, 3/12/08, Marc Fromm <Marc.Fromm@wwu.edu> wrote:
> From: Marc Fromm <Marc.Fromm@wwu.edu>
> Subject: [ADMIN] restore a table in a database
> To: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
> Date: Wednesday, 3 December, 2008, 7:21 PM
> How do I restore just a table to a database? I was able to
> create a backup of the required table from a backup of the
> database as follows.
> I don't know how to properly restore the backed up
> table "departments" to the original
> "sms" database.
>
> create a new db
> createdb -U postgres smstest
>
> restore a backup of the db to the new db
> psql -U postgres smstest <
> /tmp/postgresql-sms-11-24-2008_04-05-database
>
> backup up the specific table from the restored db
> pg_dump -U postgres -a -t departments smstest >
> /tmp/smsdepartments
>
> The table backup is smsdepartments. How do I restore
> smsdepartments to the table called departments in the sms
> database?
>
> Thanks
>
> Marc
Open up the backup file and change the tablename in the CREATE and COPY commands from smsdepartments to departments. Then restore with:
psql -U postgres smstest < /tmp/smsdepartments
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2008-12-04 19:18 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-12-03 19:21 restore a table in a database Marc Fromm <Marc.Fromm@wwu.edu>
2008-12-04 13:56 ` val <valiouk@yahoo.co.uk>
2008-12-04 19:18 ` Marc Fromm <Marc.Fromm@wwu.edu>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox