Обсуждение: Specify tables to be backed up

Поиск
Список
Период
Сортировка

Specify tables to be backed up

От
"Rob Richardson"
Дата:
Greetings!
 
The PostGRES database our application relies on takes about an hour to back up and another hour to restore.  It doesn't have very many tables (about 80), and most don't have very many records.  The time is consumed by a couple of tables that are likely to have hundreds of thousands of records, and at least one of those is indexed.  Those records contain historical data that is not needed for testing.  I would like to be able to perform a backup that includes the schema for those tables but does not include their records.  As a test, I tried running pg_dump using -t twice, hoping to get a file that contained backup information for two table.  I only got one table.  I'm thinking the only way of doing what I want to do is to have a batch file that contains a string of pg_dump calls, something along the lines of:
 
pg_dump -t table1 MyDatabase > mydump.backup
pg_dump -t table2 MyDatabase >> mydump.backup
pg_dump -t -s big_table_I_only_want_the_schema_of MyDatabase >> mydump.backup
 
Will that work?  Is there a better way?
 

Robert D. Richardson
Product Engineer Software

RAD-CON, Inc.
TECHNOLOGY: Innovative & Proven
Phone : +1.216.706.8905
Fax:  +1.216.221.1135
Website:  www.RAD-CON.com
E-mail:  rob.richardson@RAD-CON.com

 
Вложения

Re: Specify tables to be backed up

От
Tom Lane
Дата:
"Rob Richardson" <Rob.Richardson@rad-con.com> writes:
> I'm thinking the only way of doing what I
> want to do is to have a batch file that contains a string of pg_dump
> calls, something along the lines of:

> pg_dump -t table1 MyDatabase > mydump.backup
> pg_dump -t table2 MyDatabase >> mydump.backup
> pg_dump -t -s big_table_I_only_want_the_schema_of MyDatabase >>
> mydump.backup

> Will that work?  Is there a better way?

Recent versions of pg_dump have switches to exclude specific tables.
Consider
    pg_dump --exclude-table=big_table ...
    pg_dump -s -t big_table ...

            regards, tom lane