In AWS you need to use –dbname, not –d.
AWS postgres is their own, they have rewritten Postgres to their needs, you need to use AWS Postgres commands, not regular Postgres command. Some work, some don’t.
Best practices for migrating PostgreSQL databases to Amazon RDS and Amazon Aurora | AWS Database Blog
From: M Sarwar <sarwarmd02@xxxxxxxxxxx>
Sent: Tuesday, October 24, 2023 10:18 AM
To: Wetmore, Matthew (CTR) <Matthew.Wetmore@xxxxxxxxxxxxxxxxxxx>; Scott Ribe <scott_ribe@xxxxxxxxxxxxxxxx>
Cc: pgsql-admin@xxxxxxxxxxxxxx
Subject: [EXTERNAL] Re: pg_restore is not working
I tried this approach. It is generating error when I am using the format of < filename with pg_restore.
Database location: aws/rds
Client where I am running pg-dump and pg-restore is from Windows laptop.
Pg_restore …blah.. < dumpfile.sql
When I tried to add -f in the pg_restore clause, it is generating error.
I tried to use another clause --file=file-name which is also generating errors.
PS H:\> pg_restore -F t -v -h bronxdb1.c52p1f6zjuib.us-gov-west-1.rds.amazonaws.com -U db_sarwar -d bronxdb1
-t 'bronxdev.errors' --file=C:\Mohammed\Work\NGC\Apps\Bronx\Backups\Dumps\data\backup_Dev_Bronxdev-Errors_20231024-5.DUMP
pg_restore: error: options -d/--dbname and -f/--file cannot be used together
Try "pg_restore --help" for more information.
PS H:\>
> On Oct 24, 2023, at 10:43 AM, M Sarwar <sarwarmd02@xxxxxxxxxxx> wrote:
>
>
> Hi,
>
> Restore is having no errors but it is not restoring any data.
> I have used the below command pg_dump
> pg_dump -F t -v -h bronxdb1.c52p1yyyjuib.us-xxx-west-1.rds.amazonaws.com -U db_sarwar -d NGBRDVDB -t 'bronxdev.errors' -f C:\Mohammed\Work\NGC\Apps\Bronx\Backups\Dumps\data\backup_Dev_Bronxdev-Errors_20231024-5.DUMP
>
> I am using the below command for the restore.
>
> PS H:\> pg_restore -F t -v -h bronxdb1.c52p1fyyyuib.us-xxx-west-1.rds.amazonaws.com -U db_sarwar -d bronxdb1 -t 'bronxdev.errors' C:\Mohammed\Work\NGC\Apps\Bronx\Backups\Dumps\data\backup_Dev_Bronxdev-Errors_20231024-5.DUMP
> pg_restore: connecting to database for restore
> Password:
> pg_restore: implied data-only restore
> PS H:\>
>
> Thanks,
> Sarwar
You're missing the -f on the pg_restore to take the dump from a file, so it's sitting waiting for SQL to come via stdin.
|
