Re: restore a table in a database

From: Marc Fromm <Marc(dot)Fromm(at)wwu(dot)edu>
To: "valiouk(at)yahoo(dot)co(dot)uk" <valiouk(at)yahoo(dot)co(dot)uk>
Cc: "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org>
Subject: Re: restore a table in a database
Date: 2008-12-04 19:18:32
Message-ID: B0D7C0A3F35FE144A70312D086CBCA9B0213A92F00@ExchMailbox2.univ.dir.wwu.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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(at)yahoo(dot)co(dot)uk]
Sent: Thursday, December 04, 2008 5:57 AM
To: pgsql-admin(at)postgresql(dot)org; Marc Fromm
Subject: Re: [ADMIN] restore a table in a database

--- On Wed, 3/12/08, Marc Fromm <Marc(dot)Fromm(at)wwu(dot)edu> wrote:

> From: Marc Fromm <Marc(dot)Fromm(at)wwu(dot)edu>
> Subject: [ADMIN] restore a table in a database
> To: "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)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

In response to

Browse pgsql-admin by date

  From Date Subject
Next Message Abubaker 2008-12-05 08:43:25 Remote access
Previous Message MStringham 2008-12-04 18:27:37 "PANIC: btree_split_redo: lost left sibling" problem