Why does pg_restore fail silently in AWS ECS?

Question

Consider this pg_restore command:

pg_restore -h 123.123.123.123 -d my_database -U my_user --no-owner --no-privileges --no-password -t specific_table 616f6d35-202104.backup

When I run it locally it works. But when I run it my ECS instance it does not restore. There is no error and exit code is 0.

If I create the db in the statement (by adding the --create flag and changing my_database to postgres) like so:

pg_restore -h 123.123.123.123 -d postgres -U my_user --create --no-owner --no-privileges --no-password -t specific_table 616f6d35-202104.backup

It still works locally. When I run it in ECS it creates the db but still does not restore the table. And if I exclude the specific table:

pg_restore -h 123.123.123.123 -d my_database -U my_user --no-owner --no-privileges --no-password 616f6d35-202104.backup

Then it works. But it loads the whole database which I don't want. So it's something to do with the -t flag that works locally but not in my ECS instance.

EDIT: There does seem to be a version mis-match. The closest I could get the environments was PostgreSQL v10.16 on the ECS and v10.4 on the target EC2 that has the Postgres server 123.123.123.123. Still does not work. Could this be the issue? If so how do I install a specific minor version of postgresql or postgresql-client?

For the ECS in the docker file I have:

RUN apt-get update
RUN apt-get install -y curl ca-certificates gnupg
RUN curl https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -
RUN echo "deb http://apt.postgresql.org/pub/repos/apt buster-pgdg main" | tee /etc/apt/sources.list.d/docker.list
RUN apt-get update
RUN apt-get install -y postgresql-10

This gets me to v10.16 on the ECS (which still doesn't work). But if I change that last line to specify a specific minor version (to more closely match the EC2's version) it can't find it.

RUN apt-get install -y postgresql-10.5

E: Unable to locate package postgresql-10.5
E: Couldn't find any package by glob 'postgresql-10.5'
E: Couldn't find any package by regex 'postgresql-10.5'

Again, locally it works - and the version I have locally is v10.5.

EDIT-2: I have a little more visibility on this. Looks like it outputs these two lines and then stops - still no error:

pg_restore: connecting to database for restore
pg_restore: implied data-only restore

It seems 'implied data-only restore' might not be causing an issue though - there seems to be output that should come after it. But output just stops in my case.

Answer 1

I never figured out why the -t/--table flag wasn't working remotely. But I found a different solution that did exactly what I needed. I did this in Python but it can be done in bash or other languages.

I switched to the -l list/-L use-list flag.

 os.system(f'pg_restore -l {backup_file} > list1.list')

pg_restore -l outputs a .list file that looks something like this:

;
; Archive created at 2021-04-23 08:06:25
;     dbname: abc
;     TOC Entries: 2069
;     Compression: -1
;     Dump Version: 1.13-0
;     Format: CUSTOM
;     Integer: 4 bytes
;     Offset: 8 bytes
;     Dumped from database version: 9.6.16
;     Dumped by pg_dump version: 9.6.16
;
;
; Selected TOC Entries:
;
7380; 1262 16390 DATABASE - db9j0aedvewevi6fw ury3p1vgqirfm2wq
3; 2615 2200 SCHEMA - public ury3p1vgqirfm2wq
7381; 0 0 COMMENT - SCHEMA public ury3p1vgqirfm2wq
1; 3079 13308 EXTENSION - plpgsql 
7382; 0 0 COMMENT - EXTENSION plpgsql 
185; 1259 168687 TABLE public tableA ury3p1vgqirfm2wq
186; 1259 168693 TABLE public tableB ury3p1vgqirfm2wq
187; 1259 168699 TABLE public tableC ury3p1vgqirfm2wq
188; 1259 168702 TABLE public tableZ ury3p1vgqirfm2wq

The intention of this file is so that developers can restore by exclusion. They can peek in this file and comment out using ; lines they don't want. Then they use -L/--use-list like pg_restore --use-list=my_list.list -U username ....

Since all I needed was a few tables and their data (and needed to do it programmatically) I used the generated .list file to generate my own, abbreviated file. I learned (by trial and error) that the lines responsible for creating the table and populating its data say: TABLE public tablename and TABLE DATA public tablename respectively.

table_create = 'TABLE public '
table_data = 'TABLE DATA public '

So I looped over the .list file provided by pg_restore -l and looked for the lines for the needed tables. When I found the right lines I added them to a list.

table_names = ['tablename1', 'tablename2']
full_list = 'list1.list'
list_lines = []
with open(full_list) as topo_file:
    for line in topo_file:
        for table_name in table_names:
            if table_data + table_name + ' ' in line or table_create + table_name + ' ' in line:
                print(line)
                list_lines.append(line)

Then I used that list to create my own .list file.

restore_list = 'list2.list'
with open(restore_list, 'w') as f:
    for item in list_lines:
        f.write("%s\n" % item)

I found the header was not necessary. So if I only wanted one table and its data the file would look very simple:

426; 1259 169557 TABLE public tablename1 ury3p1vgqirfm2wq
7130; 0 169557 TABLE DATA public tablename1 ury3p1vgqirfm2wq

If you notice the ; comments things out. So anything after it pg_restore doesn't care about. And finally I used that list file for in the pg_restore command.

restore_command = f'pg_restore --use-list={restore_list} --host=localhost --username=postgres --dbname=test --no-owner --no-privileges --verbose "{backup_file}"'
os.system(restore_command)

pg_restore docs were the key - although I would have liked a little more context around the .list file syntax and structure.