Обсуждение: pg_restore is not working

> On Oct 24, 2023, at 10:43 AM, M Sarwar <sarwarmd02@outlook.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.

P {margin-top:0;margin-bottom:0;}

 

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

 

Hi,

On Tue, Oct 24, 2023 at 05:25:02PM +0000, Wetmore, Matthew (CTR) wrote:
> In AWS you need to use –dbname, not –d.

-d is the short option for the --dbname long option:

|$ LANG=C pg_restore --help | grep dbname
|  -d, --dbname=NAME        connect to database name


Michael

P {margin-top:0;margin-bottom:0;}

Hi Ron,

I did try using -Fc clause. pg_dump is running fine but pg_restore is having the same issue.

I tried once again now after seeing your email and getting the same scenario.

Pg_Dump:-

PS H:\> pg_dump -Fc -v -h sharded -U db_sarwar -d NGBRDVDB -t 'bronxdev.errors' -f C:\Mohammed\Work\NGC\Apps\Bronx\Backups\Dumps\data\backup_Dev_Bronxdev-Errors_20231024-6.DUMP

Password:

 

Pg_dump ran fine.

 

Pg_Restore:-

 

PS H:\> pg_restore -v -h sharded  -U db_sarwar -d bronxdb1 -t 'bronxdev.errors' C:\Mohammed\Work\NGC\Apps\Bronx\Backups\Dumps\data\backup_Dev_Bronxdev-Errors_20231024-6.DUMP

pg_restore: connecting to database for restore

Password:

pg_restore: implied data-only restore

PS H:\> pg_restore -Fc -v -h sharded  -U db_sarwar -d bronxdb1 -t 'bronxdev.errors' C:\Mohammed\Work\NGC\Apps\Bronx\Backups\Dumps\data\backup_Dev_Bronxdev-Errors_20231024-6.DUMP

pg_restore: connecting to database for restore

Password:

pg_restore: implied data-only restore

PS H:\>

 

It has no errors but it did not restore the data.

SrcDir=/var/lib/pgsql/backups/test

SrcDB=somedb

DestDB=anotherdb

Tbl=public.job

pg_dump --host=$SrcHost -Fc -t $Tbl -v -d ${SrcDB} -f $SrcDir/${Tbl}.dump pg_restore --host=$DestHost -v -d ${DestDB} ${SrcDir}/${Tbl}.dump

Thanks,

Sarwar

---

From: Ron <ronljohnsonjr@gmail.com>
Sent: Tuesday, October 24, 2023 1:08 PM
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Re: pg_restore is not working

 

On 10/24/23 11:43, M Sarwar wrote:

p {margin-top:0; margin-bottom:0}

 

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

Why "-Ft" instead of "-Fc" for a .dump file?

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

 

--
Born in Arizona, moved to Babylonia.

P {margin-top:0;margin-bottom:0;}

Here is my update.

I was using pg_restore with -t 'schema-name.tablename'.

After reading the document, https://www.postgresql.org/docs/16/app-pgrestore.html, I tried to avoid using the clause of -t in pg-restore command. I just restored the entire which has just 1 table dump.

In my opinion if there is any issue with -t clause in pg_restore, it should through an error instead of it is simply coming out without throwing any error.

Thanks,

Sarwar

---

From: Ron <ronljohnsonjr@gmail.com>
Sent: Tuesday, October 24, 2023 1:56 PM
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Re: pg_restore is not working

 

On 10/24/23 12:24, M Sarwar wrote:

p {margin-top:0; margin-bottom:0}

Hi Ron,

I did try using -Fc clause. pg_dump is running fine but pg_restore is having the same issue.

I tried once again now after seeing your email and getting the same scenario.

Pg_Dump:-

PS H:\> pg_dump -Fc -v -h sharded -U db_sarwar -d NGBRDVDB -t 'bronxdev.errors' -f C:\Mohammed\Work\NGC\Apps\Bronx\Backups\Dumps\data\backup_Dev_Bronxdev-Errors_20231024-6.DUMP

Password:

 

Pg_dump ran fine.

 

Pg_Restore:-

 

PS H:\> pg_restore -v -h sharded  -U db_sarwar -d bronxdb1 -t 'bronxdev.errors' C:\Mohammed\Work\NGC\Apps\Bronx\Backups\Dumps\data\backup_Dev_Bronxdev-Errors_20231024-6.DUMP

pg_restore: connecting to database for restore

Password:

pg_restore: implied data-only restore

PS H:\> pg_restore -Fc -v -h sharded  -U db_sarwar -d bronxdb1 -t 'bronxdev.errors' C:\Mohammed\Work\NGC\Apps\Bronx\Backups\Dumps\data\backup_Dev_Bronxdev-Errors_20231024-6.DUMP

pg_restore: connecting to database for restore

Password:

pg_restore: implied data-only restore

PS H:\>

 

It has no errors but it did not restore the data.

This works for me.  I just tested it.  You'll have to convert from bash to Windows.

SrcHost=somehost
DescHost=anotherhost

SrcDir=/var/lib/pgsql/backups/test

SrcDB=somedb

DestDB=anotherdb

Tbl=public.job

pg_dump --host=$SrcHost -Fc -t $Tbl -v -d ${SrcDB} -f $SrcDir/${Tbl}.dumppg_restore --host=$DestHost -v -d ${DestDB} ${SrcDir}/${Tbl}.dump

Thanks,

Sarwar

---

From: Ron <ronljohnsonjr@gmail.com>
Sent: Tuesday, October 24, 2023 1:08 PM
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Re: pg_restore is not working

 

On 10/24/23 11:43, M Sarwar wrote:

p {margin-top:0; margin-bottom:0}

 

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

Why "-Ft" instead of "-Fc" for a .dump file?

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

 

--
Born in Arizona, moved to Babylonia.

--
Born in Arizona, moved to Babylonia.

P {margin-top:0;margin-bottom:0;}

In both pg_dump and pg_restore commands, I am always using -v option which verbose mode. Without that I will not be able to know any status.

All of my commands are using -v clause.

Thanks,

Sarwar

---

From: Ron <ronljohnsonjr@gmail.com>
Sent: Tuesday, October 24, 2023 3:43 PM
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Re: pg_restore is not working

 

You're still doing something wrong.  And "pg_restore --verbose" does throw errors.

On 10/24/23 14:13, M Sarwar wrote:

p {margin-top:0; margin-bottom:0}

Here is my update.

I was using pg_restore with -t 'schema-name.tablename'.

After reading the document, https://www.postgresql.org/docs/16/app-pgrestore.html, I tried to avoid using the clause of -t in pg-restore command. I just restored the entire which has just 1 table dump.

In my opinion if there is any issue with -t clause in pg_restore, it should through an error instead of it is simply coming out without throwing any error.

Thanks,

Sarwar

---

From: Ron <ronljohnsonjr@gmail.com>
Sent: Tuesday, October 24, 2023 1:56 PM
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Re: pg_restore is not working

 

On 10/24/23 12:24, M Sarwar wrote:

p {margin-top:0; margin-bottom:0}

Hi Ron,

I did try using -Fc clause. pg_dump is running fine but pg_restore is having the same issue.

I tried once again now after seeing your email and getting the same scenario.

Pg_Dump:-

PS H:\> pg_dump -Fc -v -h sharded -U db_sarwar -d NGBRDVDB -t 'bronxdev.errors' -f C:\Mohammed\Work\NGC\Apps\Bronx\Backups\Dumps\data\backup_Dev_Bronxdev-Errors_20231024-6.DUMP

Password:

 

Pg_dump ran fine.

 

Pg_Restore:-

 

PS H:\> pg_restore -v -h sharded  -U db_sarwar -d bronxdb1 -t 'bronxdev.errors' C:\Mohammed\Work\NGC\Apps\Bronx\Backups\Dumps\data\backup_Dev_Bronxdev-Errors_20231024-6.DUMP

pg_restore: connecting to database for restore

Password:

pg_restore: implied data-only restore

PS H:\> pg_restore -Fc -v -h sharded  -U db_sarwar -d bronxdb1 -t 'bronxdev.errors' C:\Mohammed\Work\NGC\Apps\Bronx\Backups\Dumps\data\backup_Dev_Bronxdev-Errors_20231024-6.DUMP

pg_restore: connecting to database for restore

Password:

pg_restore: implied data-only restore

PS H:\>

 

It has no errors but it did not restore the data.

This works for me.  I just tested it.  You'll have to convert from bash to Windows.

SrcHost=somehost
DescHost=anotherhost

SrcDir=/var/lib/pgsql/backups/test

SrcDB=somedb

DestDB=anotherdb

Tbl=public.job

pg_dump --host=$SrcHost -Fc -t $Tbl -v -d ${SrcDB} -f $SrcDir/${Tbl}.dumppg_restore --host=$DestHost -v -d ${DestDB} ${SrcDir}/${Tbl}.dump

Thanks,

Sarwar

---

From: Ron <ronljohnsonjr@gmail.com>
Sent: Tuesday, October 24, 2023 1:08 PM
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Re: pg_restore is not working

 

On 10/24/23 11:43, M Sarwar wrote:

p {margin-top:0; margin-bottom:0}

 

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

Why "-Ft" instead of "-Fc" for a .dump file?

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

 

--
Born in Arizona, moved to Babylonia.

--
Born in Arizona, moved to Babylonia.

--
Born in Arizona, moved to Babylonia.