AWS RDS PostgreSQL dump/restore - syntax error

Question

I am trying to import a postgresql database from an EC2 instance to RDS on the same subnet, so far I am doing the following...

1) Dump EC2 DB:

pg_dump --host localhost --port 5432 -Fc --encoding='UTF8' -U postgres ProcessorDB > /home/jboss/proc_dump_jan15.dump

2) Restore from EC2:

psql -f /home/jboss/proc_dump_jan15.dump --host=blahrds.11938475.eu-west-1.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=ProcessorDB

I am able to connect to the remote DB but I am getting the following errors:

psql:/home/jboss/proc_dump_jan15.dump:1: ERROR: syntax error at or near "PGDMP"
LINE 1: PGDMP
 REVOKE ALL ON SCHEMA public FROM postgres;
 ^
GRANT
GRANT
psql:/home/jboss/proc_dump_jan15.dump:5: ERROR: syntax error at or near ""
LINE 1: 'en'
 ^
psql:/home/jboss/proc_dump_jan15.dump:14: ERROR: syntax error at or near ""
LINE 1: 'free',
 ^
psql:/home/jboss/proc_dump_jan15.dump:21: ERROR: syntax error at or near ""
LINE 1: 'windows',

This goes on, then changes to:

psql:/home/jboss/proc_dump_jan15.dump:316: ERROR: invalid byte sequence for encoding "UTF8": 0xc2 0x40
psql:/home/jboss/proc_dump_jan15.dump:316: invalid command \?0?4
psql:/home/jboss/proc_dump_jan15.dump:317: invalid command \?????M?
psql:/home/jboss/proc_dump_jan15.dump:318: invalid command \)??LF,?C?{<??y??ݚ???Ɲ???0??{?dV
 ?
psql:/home/jboss/proc_dump_jan15.dump:321: ERROR: invalid byte sequence for encoding "UTF8": 0xbd
psql:/home/jboss/proc_dump_jan15.dump:331: invalid command \?`?v?ʺ??;6????6??N??.????c?~???&?G(??
psql:/home/jboss/proc_dump_jan15.dump:335: invalid command \@(??n']??sIZ??1VZ?o???????j?+??λ?=L?E??ܡ|c?0t
psql:/home/jboss/proc_dump_jan15.dump:338: invalid command \?A???N??/hL???Q??G??r
psql:/home/jboss/proc_dump_jan15.dump:354: invalid command \??I?$?R?o??¬???NJ?-F'??x?0z??z??hŗc?
psql:/home/jboss/proc_dump_jan15.dump:357: invalid command \x?r?Y??gMb~?墶6wб??m'aem?o????]X?s?????}??u?&?;-xY8?1)?}c??-@??h?K???IA?֒qv??Hs?g??J?DoD?Y?m
psql:/home/jboss/proc_dump_jan15.dump:361: invalid command \??W?]U?22?U-??N?
psql:/home/jboss/proc_dump_jan15.dump:362: invalid command 4円??im?@??ښ?S?j???ꨫ?d܊פJ?cu??l!?ް̳u?5+^O??>=?k+]???԰A??5????~??N???0??:=??ՙM?3zm
psql:/home/jboss/proc_dump_jan15.dump:372: invalid command \HF??w?
psql:/home/jboss/proc_dump_jan15.dump:378: invalid command \g?#?s?t?I,???T?}5???

Cut short for brevity...

I spotted the 'invalid byte sequence for encoding "UTF8"' and added the --encoding attribute to the dump command but that hasn't had any effect

Anyone know what the issue might be? Am I missing an option on the dump? The AWS docs are a bit vague here

Nb. the EC2 database is PG 9.0 and the RDS database is PG 9.3.5 but I assume this is ok as the dump/restore will deal with the migration?

Answer 1

A pg_dump output with the -Fc (custom) is not a SQL script, it's a compressed archive that only pg_restore is able to handle.

You may either pass the dump file to pg_restore and pipe its output to psql (often done that way and close to what you tried):

pg_restore /home/jboss/proc_dump_jan15.dump | psql --host=blahrds [other options]...

...or use the -d,-h,... and other options of pg_restore to have it connect to the database and skip the psql invocation.

See pg_restore manpage for 9.3 for details.

Nb. the EC2 database is PG 9.0 and the RDS database is PG 9.3.5 but I assume this is ok as the dump/restore will deal with the migration?

The recommended method is to use pg_dump 9.3 against the 9.0 database to create the dump. The reasoning is that newer versions always know how to extract older databases such that they will import flawlessly into their version. The opposite is not true, since obviously at the time when pg_dump 9.0 is released, the authors don't know what compatibility problems future versions might bring.

• DaveB: Thanks @Daniel that makes perfect sense, I will try tomorrow....regarding the pg_dump 9.3, do you know if this is possible from RDS? I assume not because I cannot access the /bin dir? Im guessing I would need to install 9.3 on the EC2 instance to do the dump against the 9.0 DB?
• DaveB: Ok I just installed 9.3 on the EC2 instance and did the dump that way...import worked fine, thank you