How to Partially Restore a PostgreSQL Database? - Yiming Chen

[utterances-bot]

How to Partially Restore a PostgreSQL Database? - Yiming Chen

There are two options provided by pg_restore to control which tables to restore. I'd share my mistakes with using pg_restore -t and explain why pg_restore -l/-L is the right solution.

https://yiming.dev/blog/2020/01/13/how-to-partially-restore-a-postgre-sql-database/

[aussiDavid]

I got caught by this. Your solution worked. Thanks for sharing :)

[rokdd]

Thanks for sharing, that is really good and exactly my usecase. I was wondering how you replace the names of database when you copy it through the stages? I used the plain format in the past and replaced the names of the database?

[dsdshcym]

@rokdd Glad to know this post from almost 4 years ago is still useful to you.

pg_restore provides an option -d to specify which database to restore the data to:

    -d dbname
    --dbname=dbname
    Connect to database dbname and restore directly into the database. The dbname can be a connection string. If
    so, connection string parameters will override any conflicting command line options.

So you may run pg_dump in production database {app-name}_production, then pg_restore -d {app-name}_staging on your staging environment.

[rokdd]

@dsdshcym Well around postgres it is sometimes difficult to find the right things among all the standard questions. So your post is really helpful ;-)

Well yes we modify the schema so sadly it is kind of needed to replace. But I found a way to convert back to plain format. I will post my solution as comment when it will work.

[rokdd]

So what I did:

# create the dump of the database
pg_dump -E UTF8 -c --format=custom --file=SRC_DATABASE-download.dump SRC_DATABASE

#create the TOC of the dump
pg_restore -l SRC_DATABASE-download.dump  > SRC_DATABASE-download.toc

#comment the not needed tables out by rules you need
sed ... > SRC_DATABASE-download-replaced.toc

#convert back to plain sql from dump
pg_restore -c -f SRC_DATABASE-download.sql -L SRC_DATABASE-replaced.toc SRC_DATABASE-download.dump

#replace in the sql file now.. the old database name with the new one
sed ... SRC_DATABASE-download.sql > DST_DATABASE-download_filtered_toc.sql

#load to postgres
psql .. < DST_DATABASE-download_filtered_toc.sql

Thanks again for your post and your help!