Обсуждение: 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.
Thenrestore with: 
psql -U postgres smstest < /tmp/smsdepartments

> 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
ofthe database sms. The backup file smsdepartments (a backup of the departments table extracted from database smstest)
onlyhas 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
withthe 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
fromthe 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
extensiveedits to the backup of the table file, without actually explaining what edits to make. I made no edits and it
appearsthe 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.
Thenrestore with: 
psql -U postgres smstest < /tmp/smsdepartments