RE: pg_restore is not working

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<https://aws.amazon.com/blogs/database/best-practices-for-migrating-postgresql-databases-to-amazon-rds-and-amazon-aurora/>

From: M Sarwar <sarwarmd02(at)outlook(dot)com>
Sent: Tuesday, October 24, 2023 10:18 AM
To: Wetmore, Matthew (CTR) <Matthew(dot)Wetmore(at)express-scripts(dot)com>; Scott Ribe <scott_ribe(at)elevated-dev(dot)com>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: [EXTERNAL] Re: pg_restore is not working

Hi Matt,
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.

Thanks,
Sarwar

________________________________
From: Wetmore, Matthew (CTR) <Matthew(dot)Wetmore(at)express-scripts(dot)com<mailto:Matthew(dot)Wetmore(at)express-scripts(dot)com>>
Sent: Tuesday, October 24, 2023 1:05 PM
To: M Sarwar <sarwarmd02(at)outlook(dot)com<mailto:sarwarmd02(at)outlook(dot)com>>; Scott Ribe <scott_ribe(at)elevated-dev(dot)com<mailto:scott_ribe(at)elevated-dev(dot)com>>
Cc: pgsql-admin(at)postgresql(dot)org<mailto:pgsql-admin(at)postgresql(dot)org> <pgsql-admin(at)postgresql(dot)org<mailto:pgsql-admin(at)postgresql(dot)org>>
Subject: RE: pg_restore is not working

Pg_restore …blah.. < dumpfile.sql

From: M Sarwar <sarwarmd02(at)outlook(dot)com<mailto:sarwarmd02(at)outlook(dot)com>>
Sent: Tuesday, October 24, 2023 10:03 AM
To: Scott Ribe <scott_ribe(at)elevated-dev(dot)com<mailto:scott_ribe(at)elevated-dev(dot)com>>
Cc: pgsql-admin(at)postgresql(dot)org<mailto:pgsql-admin(at)postgresql(dot)org>
Subject: [EXTERNAL] Re: pg_restore is not working

Hi Scott,

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:\>

Thanks,

Sarwar

________________________________

From: Scott Ribe <scott_ribe(at)elevated-dev(dot)com<mailto:scott_ribe(at)elevated-dev(dot)com>>
Sent: Tuesday, October 24, 2023 12:56 PM
To: M Sarwar <sarwarmd02(at)outlook(dot)com<mailto:sarwarmd02(at)outlook(dot)com>>
Cc: pgsql-admin(at)postgresql(dot)org<mailto:pgsql-admin(at)postgresql(dot)org> <pgsql-admin(at)postgresql(dot)org<mailto:pgsql-admin(at)postgresql(dot)org>>
Subject: Re: pg_restore is not working

> On Oct 24, 2023, at 10:43 AM, M Sarwar <sarwarmd02(at)outlook(dot)com<mailto:sarwarmd02(at)outlook(dot)com>> 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.