How to Partially Restore a PostgreSQL Database?
Tags: ruby, postges, database, rails
Published: 2020-01-15 01:14:36

---
title: How to Partially Restore a PostgreSQL Database?
published: true
date: 2020-01-14 00:00:00 UTC
tags: ruby, postges, database, rails
canonical_url: https://yiming.dev/blog/2020/01/13/how-to-partially-restore-a-postgre-sql-database
---

<section id="content" role="main">
    <div id="outline-container-sec-" style="padding-top: 70px">
        <div></div>
           
Recently, I need to restore a PostgreSQL Database from production to staging.
Unfortunately, our staging server is not as powerful as our production server.
Specifically, our staging only has ~800GB of disk storage.
But the total spaces needed for production database is slightly over 800GB.
And due to some technical constraints, we can't add more spaces to staging.
So we choose to do a partial restore, i.e. only restoring data from tables we needed, excluding data we didn't need.
This task turns out to be harder than I expected.
I've made many mistakes until I find the correct way to do it.
Hope you can learn from my mistakes and restore your data correctly.


<div id="outline-container-org7aaa3fe">
<h2 id="org7aaa3fe">TL;DR</h2>
<div id="text-org7aaa3fe">
<p>
Don't use <code>pg_restore -t</code> for a clean restore!
Don't use <code>pg_restore -t</code> for a clean restore!
Don't use <code>pg_restore -t</code> for a clean restore!
</p>

<ol>
<li>Use <code>pg_restore -l</code> to dump a table of contents of the archive.</li>
<li>Comment out unneeded DATA from the ToC</li>
<li>Use <code>pg_restore -L</code> to restore the whole database except DATA that were excluded in 2</li>
</ol>
</div>
</div>
<div id="outline-container-orgca6281f">
<h2 id="orgca6281f">Problems with <code>pg_restore -t</code>
</h2>
<div id="text-orgca6281f">
<p>
I searched on StackOverflow.
<a href="https://stackoverflow.com/questions/37038193/exclude-table-during-pg-restore">The first solution</a> I found was <code>pg_restore -t</code>.
So I dropped the staging db, ran the <code>pg_restore -t</code> command immediately.
</p>

<p>
But I ran into three annoying issues.
Only after I'd fixed all of them, did I realize I shouldn't have used <code>pg_restore -t</code> at the first place.
</p>

<ol>
<li>
<p>
Indices were missing
</p>

<p>
The first thing I noticed was the staging app became so slow after the restore.
Then I checked the indices in staging database and found nothing.
This was the moment I realized that the <code>-t</code> option is doing its job too well.
</p>

<p>
(Apparently, another developer has already run into the same issue before:
 <a href="https://serverfault.com/questions/806271/pg-restore-on-a-single-table-not-restoring-indexes">postgresql - pg_restore on a single table not restoring indexes - Server Fault</a>)
</p>
</li>

<li>
<p>
Constraints were missing
</p>

<p>
The next issue happened when I started using the Rails app.
Whenever I requested a record, I got the same error: <code>Unknown primary key for table</code>
</p>

<p>
This was because all the primary key CONSTRAINTS were missing (not restored) from the database.
When these constraints were missing, <code>ActiveRecord</code> didn't know <code>id</code> was the primary key, which triggered the error.
</p>

<p>
After primary key constraints were restored, this issue was fixed.
</p>
</li>

<li>
<p>
Sequences/Defaults were missing
</p>

<p>
Finally, I got another issue about <code>id</code>.
Whenever I saved a record, I got this error: <code>ActiveRecord::StatementInvalid: PG::Error: ERROR: null value in column “id” violates not-null constraint</code>
</p>

<p>
This was because all the SEQUENCES and DEFAULTS for the <code>id</code> columns were missing (not restored) from the database.
<code>ActiveRecord</code> uses SEQUENCES and DEFAULTS to make primary keys automatically increment itself when a new record is created in the db.
When these two were missing from the database, <code>ActiveRecord</code> could only assign primary key to <code>nil</code>, which violated the constraint.
</p>

<p>
More interestingly, I only restored the <code>SEQUENCES</code> for <code>id</code> columns.
But the issue still persisted.
So I also tried to <a href="http://www.osny.me/blog/how-to-reset-postgres-id-sequence-in-a-ruby-on-rails-app">reset the <code>SEQUENCES</code> (which didn't work because <code>DEFAULTS</code> were still missing):</a>
</p>
<div>
<pre>ActiveRecord::Base.connection.tables.each do |t|
  ActiveRecord::Base.connection.reset_pk_sequence!(t)
end
</pre>
</div>

<p>
After I restored <code>DEFAULTS</code>, the issue was fixed.
</p>
</li>
</ol>

<p>
After these three annoying issues, I knew I did something wrong.
Fortunately, it was only our staging server, so I only wasted my own time and energy.
</p>

<p>
Reading the documentation for <code>pg_restore -t</code>, I finally realized it wasn't the right tool for the job.
</p>
<pre>
-t table
--table=table
    Restore definition and/or data of only the named table. For this purpose,
    "table" includes views, materialized views, sequences, and foreign tables.
    Multiple tables can be selected by writing multiple -t switches. This
    option can be combined with the -n option to specify table(s) in a
    particular schema.

        Note
        When -t is specified, pg_restore makes no attempt to restore any other
        database objects that the selected table(s) might depend upon.
        Therefore, there is no guarantee that a specific-table restore into a
        clean database will succeed.
</pre>
<p>
As you may have notices from the <code>Note</code>, pg_restore makes no attempt to restore any other database objects that the selected table(s) might depend upon.
(I didn't read this Note because I thought <a href="https://stackoverflow.com/questions/37038193/exclude-table-during-pg-restore">StackOverflow</a> was trustworthy.)
</p>

<p>
Besides all the missing indices, constraints, sequences, defaults, and more, <code>pg_restore -t</code> won't create any tables that were not specified with the <code>-t</code> option.
So any db migration related to the missing tables would fail on staging in the future.
Which means this solution is absolutely unacceptable in this use case.
</p>

<p>
What else shall we do?
</p>
</div>
</div>
<div id="outline-container-org43e973a">
<h2 id="org43e973a">Introducing <code>pg_restore -l</code> and <code>pg_restore -L</code>
</h2>
<div id="text-org43e973a">
<p>
Turns out the correct answer is right below the first one in <a href="https://stackoverflow.com/questions/37038193/exclude-table-during-pg-restore">the same StackOverflow page</a>.
</p>

<ol>
<li>
<p>
<code>pg_restore -l psql_backup.dump &gt; db.list</code>
</p>

<p>
Passing <code>-l</code> option to <code>pg_restore</code> would produce a table of content for the dump file.
This ToC includes all the data and their locations included in the dump file:
</p>
<pre>
;
; Archive created at Mon Sep 14 13:55:39 2009
; dbname: DBDEMOS
; TOC Entries: 81
; Compression: -1
; Dump Version: 1.13-0
; Format: CUSTOM
; Integer: 4 bytes
; Offset: 8 bytes
; Dumped from database version: 10.10
; Dumped by pg_dump version: 10.10
;
;
; Selected TOC Entries:
;
28920 70946 DATABASE - DBDEMOS pasha
55141; 15509 237 SCHEMA - public pasha
50798; 80741 51835 COMMENT - SCHEMA public pasha
66197; 75700 30831 ACL - public pasha
79603; 43218 86982 TYPE public composite pasha
59759; 64565 4792 EXTENSION - pg_trgm
95301; 10755 17786 COMMENT - EXTENSION pg_trgm
31637; 99705 30851 TABLE public users DBDEMOS
18171; 24739 26392 SEQUENCE public users_id_seq DBDEMOS
81822; 19526 48192 SEQUENCE OWNED BY public users_id_seq DBDEMOS
59215; 11301 6736 DEFAULT public users id DBDEMOS
2227; 53943 37511 TABLE DATA public users DBDEMOS
37029; 1384 1099 SEQUENCE SET public users_id_seq DBDEMOS
14500; 53947 96995 CONSTRAINT public users users_pkey DBDEMOS
26691; 92878 55511 INDEX public index_users_on_email DBDEMOS
85474; 88765 68415 TRIGGER public users username_update DBDEMOS
</pre>

<p>
Notice that this file contains all types of data in the dump: databases, schemas, comments, access control lists (ACL), extensions, tables, sequences, defaults, table data, constraints, indices, triggers, and so on.
</p>
</li>

<li>
<p>
Comment out data we do not need
</p>

<p>
We can change this ToC by commenting out or deleting the part we don't want to restore.
In this case, we don't want to restore <code>DATA</code> from <code>users</code> table, so we delete it.
</p>
<pre>
;
; Archive created at Mon Sep 14 13:55:39 2009
; dbname: DBDEMOS
; TOC Entries: 81
; Compression: -1
; Dump Version: 1.13-0
; Format: CUSTOM
; Integer: 4 bytes
; Offset: 8 bytes
; Dumped from database version: 10.10
; Dumped by pg_dump version: 10.10
;
;
; Selected TOC Entries:
;
28920 70946 DATABASE - DBDEMOS pasha
55141; 15509 237 SCHEMA - public pasha
50798; 80741 51835 COMMENT - SCHEMA public pasha
66197; 75700 30831 ACL - public pasha
79603; 43218 86982 TYPE public composite pasha
59759; 64565 4792 EXTENSION - pg_trgm
95301; 10755 17786 COMMENT - EXTENSION pg_trgm
31637; 99705 30851 TABLE public users DBDEMOS
18171; 24739 26392 SEQUENCE public users_id_seq DBDEMOS
81822; 19526 48192 SEQUENCE OWNED BY public users_id_seq DBDEMOS
59215; 11301 6736 DEFAULT public users id DBDEMOS
; 2227; 53943 37511 TABLE DATA public users DBDEMOS
37029; 1384 1099 SEQUENCE SET public users_id_seq DBDEMOS
14500; 53947 96995 CONSTRAINT public users users_pkey DBDEMOS
26691; 92878 55511 INDEX public index_users_on_email DBDEMOS
85474; 88765 68415 TRIGGER public users username_update DBDEMOS
</pre>
</li>

<li>
<p>
<code>pg_restore -L db.list</code>
</p>

<p>
<code>-L</code> option would tell <code>pg_restore</code> to only restore the data specified in the Table of Content file.
</p>
</li>
</ol>

<p>
After restoring with this solution, our staging behaves the same as production.
No weird issues like <code>Unknown primary key for table</code> anymore!
</p>
</div>
</div>
<div id="outline-container-orgb460ade">
<h2 id="orgb460ade">Lessons Learned</h2>
<div id="text-orgb460ade">
<p>
The biggest lesson from this experience for me is to always RTFW (read the f**king manual).
Don't trust StackOverflow or blog posts online too much.
They can be misleading more often than not.
Always read the documentation to fully understand the meaning of the script, command, option I'm going to use.
</p>

<p>
It's lucky that these issues only happened to our staging server, so they didn't impact our users.
But I need to train myself to be more careful on staging, and even on my local.
Only after good habits are cultivated, can I perform these actions (or automate them) on production safely.
</p>
</div>
</div>

    </div>
</section>