Search code examples
postgresql-9.3database-backups

Postgresql Is this type of statement allowed to backup specific tables

On Postgresql I'm trying to write a statement to backup many look up tables in the system in order to restore on another system.

./pg_dump -p 1234 -U mydbuser databaseName
    --table array_to_string(SELECT table_name
        FROM information_schema.tables
        WHERE table_name like 'pLookup%'
    )
    -f /var/temp/dbbkup/'db_lookup_tables.backup'`

I cannot figure out the syntax to avoid the error 'syntax error near unexpected token '('. Can anyone please help me figure out how to write this or even if it is possible to do so?

I don't have PGAdmin just the command line.

Solution

  • This is a way that it can be done knowing the schema and pattern and NOT using the 'information_schema.tables'.

    ./pg_dump -p 1234 -U mydbuser databaseName
    -t 'myLookupTableName%'
    -f /var/temp/dbTableBkup/'db_lookup_tables.backup'

    I'm using the % as a wildcard for matching several table names with the prefix of myLookupTableName.

    Hope this helps others out there.