Обсуждение: restore a table in a database
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
--- 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