Postgresql – AWS RDS PostgreSQL dump/restore – syntax error

amazon-rdsawspostgresqlpostgresql-9.3

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?º???????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?

Best Answer

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.

Related Question