pg_restore hangs when trying to restore an AWS RDS instance from a Linux EC2

user_2373676 0
Viewed 240

I created a serverless Aurora-Postgresql RDS cluster on AWS in a VPC, and a Linux EC2 instance to access it from, also in the VPC. I have a binary file dump.bin I dumped from a prior database with pg_dump -Fc that I want to use to restore the new database to the state of the old one. Inside the EC2 instance, I can run pg_dump against the new database with no issues, which creates a dump of the unconfigured database. I can even run pg_restore --version, and it prints pg_restore (PostgreSQL) 10.17. But when I run pg_restore with arguments to connect to the database and actually try to commit the restore, the program simply hangs indefinitely with no output.

This is the command I am running:

pg_restore --host=<rds-instance-dns-host>.us-east-2.rds.amazonaws.com --port=5432 --username=postgres --file=dump.bin --verbose
restore amazon-web-services amazon-ec2 postgresql
1 Answers

I think you mistook --file option for FILE argument in pg_restore:

$ pg_restore --help
pg_restore restores a PostgreSQL database from an archive created by pg_dump.

Usage:
  pg_restore [OPTION]... [FILE]
…
  -f, --file=FILENAME      output file name (- for stdout)
…

Similarily from man pg_restore:

-f filename
--file=filename
  Specify output file for generated script,
  or for the listing when used with -l.
  Use - for stdout.

The --file tells where to write output, an SQL script that would restore the database if run on it. It is not about from which file to restore. So your pg_restore does not know from which file to read, so it tries to read from its standard input.

So your command should rather look like this:

pg_restore --host=<rds-instance-dns-host>.us-east-2.rds.amazonaws.com \
  --port=5432 --username=postgres --verbose dump.bin

Or, as I'd recommend, use parallel restore and output to a file, so you can examine it later in case of any errors:

pg_restore --host=<rds-instance-dns-host>.us-east-2.rds.amazonaws.com \
  --jobs=8 --port=5432 --username=postgres --verbose dump.bin \
  > /tmp/pg_restore.log 2>&1
Tometzky_158620
Related