Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qvNd3-004r3m-Mq for pgsql-admin@arkaria.postgresql.org; Tue, 24 Oct 2023 20:03:53 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qvNd2-00CpIG-73 for pgsql-admin@arkaria.postgresql.org; Tue, 24 Oct 2023 20:03:52 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qvNd1-00CpHS-Hf for pgsql-admin@lists.postgresql.org; Tue, 24 Oct 2023 20:03:51 +0000 Received: from mail-yw1-x112f.google.com ([2607:f8b0:4864:20::112f]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qvNcw-0027oz-Rp for pgsql-admin@lists.postgresql.org; Tue, 24 Oct 2023 20:03:50 +0000 Received: by mail-yw1-x112f.google.com with SMTP id 00721157ae682-5a81ab75f21so48720227b3.2 for ; Tue, 24 Oct 2023 13:03:46 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1698177826; x=1698782626; darn=lists.postgresql.org; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=kJoyMfj3qQNiCPC+HdGUzJXtVX1+1SqBzpo414AzdVA=; b=PmLxod54HDJRgKH9E9DN92P9qHOZp5y2G+7madmNfIqHTIPPLKreeBvScsmTEvtIe1 BfuAg9gqR0tXonHAddARdtLrd9t5kuSQOLmUPDaHaADTMPL/u/SSoXPDNhxdYZUxeLRK +hqliVmFbNUCyf82afJkIPf0nf39UnPGuv4C249qODJfBwSOuthl+iVudQo1XSGfSLmA +8vhQ2muvI6lBfutc6QUhBQZ+tw9mv2KVph5jLrf9l0BIJlWXiovAp7e0+lWaiymW57L FRCWxmCWJi5blBMeJrKp8q37kYBAm2qcb3eDS9xdRFwjm2MGrmhvgyxJ4Gs9Ho39LYVK UZDA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1698177826; x=1698782626; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=kJoyMfj3qQNiCPC+HdGUzJXtVX1+1SqBzpo414AzdVA=; b=B7bygMO3cyGO6C8q9fuZIzZwiAIOn4SpalHf8eCLHIadD7Dq6Dtl0hmWn2/Oy6Prsd gNtmzhzvQYZLpOQuBFKzj7tVxq4rKCxreuqfxiIOsnkDWWqez3MUu8KE/TXiSYxHqQgO /uYCSX2Jj3WTcC3YkcM7W/jbUYGyKO5dK+cBxQvKMr0B9QC/4zgzKuK3X2utxJxN1jLD MDdl3eJ+IX5zhp7L1uQAcfAuk7lPAwgkJs6BUT6Wle7oCc74GYF1lWVjhSfyDXjqtoOc 90jy6hARzwpEBrCKVJcLUXWmEDZ+pnqDqB9FeiUUbe5HSyyTW/bsftLblqMxWMd5M9Nn vWbw== X-Gm-Message-State: AOJu0Yx+BbEhoCUnVvcdR3ccjvsqEBI71YQvSEkVxkJqXFiPx/4p+b3V O6ZrssPBP6zeZRxludebCoVSA0RYy8o= X-Google-Smtp-Source: AGHT+IHFI1mgAom78iogvUIzixYYHJ2FEDVoqcyU1UMoRAZXRVWtZ5xPE6YIccUGlm2ZIlqD8jSgcg== X-Received: by 2002:a05:690c:d81:b0:5a7:bde8:51d5 with SMTP id da1-20020a05690c0d8100b005a7bde851d5mr18696668ywb.32.1698177825810; Tue, 24 Oct 2023 13:03:45 -0700 (PDT) Received: from ?IPV6:2600:1700:bd2c:b810:990b:551:8028:180e? ([2600:1700:bd2c:b810:990b:551:8028:180e]) by smtp.googlemail.com with ESMTPSA id z65-20020a814c44000000b005a8eadbadbesm630953ywa.19.2023.10.24.13.03.45 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Tue, 24 Oct 2023 13:03:45 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------HmC0a4GR39lRnYirYKMtr2EF" Message-ID: <27670a63-9dce-4b46-a56b-26cdc788ba06@gmail.com> Date: Tue, 24 Oct 2023 15:03:45 -0500 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: pg_restore is not working Content-Language: en-US To: "pgsql-admin@lists.postgresql.org" References: <0100018b62722391-65a890bf-5d6b-415f-8831-bf8ae2a09fde-000000@email.amazonses.com> From: Ron In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------HmC0a4GR39lRnYirYKMtr2EF Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit If you were, then you'd see all sorts of output from pg_dump and pg_restore. On 10/24/23 14:55, M Sarwar wrote: > 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 > *Sent:* Tuesday, October 24, 2023 3:43 PM > *To:* 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: >> 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 >> *Sent:* Tuesday, October 24, 2023 1:56 PM >> *To:* pgsql-admin@lists.postgresql.org >> >> >> *Subject:* Re: pg_restore is not working >> On 10/24/23 12:24, M Sarwar wrote: >>> 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}.dump >> >> pg_restore --host=$DestHost -v -d ${DestDB} ${SrcDir}/${Tbl}.dump >> >>> Thanks, >>> Sarwar >>> >>> ---------------------------------------------------------------------------- >>> *From:* Ron >>> *Sent:* Tuesday, October 24, 2023 1:08 PM >>> *To:* pgsql-admin@lists.postgresql.org >>> >>> >>> *Subject:* Re: pg_restore is not working >>> On 10/24/23 11:43, M Sarwar 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 >>>> >>> >>> 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. -- Born in Arizona, moved to Babylonia. --------------HmC0a4GR39lRnYirYKMtr2EF Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit
If you were, then you'd see all sorts of output from pg_dump and pg_restore.

On 10/24/23 14:55, M Sarwar wrote:
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:
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:
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}.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:
 

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.

--
Born in Arizona, moved to Babylonia.
--------------HmC0a4GR39lRnYirYKMtr2EF--