Re: pg_dump pg_restore hanging in CentOS for large data

On 03/17/2017 12:27 AM, Sridevi B wrote:
Ccing list.
Please reply to list also, it puts more eyes on the problem.

> Hi Adrian,
>
> Sorry for delay. Please find my answers inline.
>
> Thanks,
> Sridevi
>
>
>
>
>
> On Thu, Mar 16, 2017 at 2:28 AM, Adrian Klaver
> <adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>> wrote:
>
> On 03/14/2017 09:48 AM, Sridevi B wrote:
>
> Hi ,
>
> I am facing an issue with backup/Restore for data size more than
> *2GB*. Its working fine for *1GB*.
>
>
>
> Below are the details for issue:
>
>
>
> Description:
>
>
>
> The command pg_dump is hanging at saving large objects and
> process gets
> terminated after some time.
>
>
>
> The command pg_restore is hanging at executing BLOB and getting
> terminated after some time.
>
>

> When you refer to BLOB do you mean large objects:
>
> https://www.postgresql.org/docs/9.2/static/largeobjects.html
> <https://www.postgresql.org/docs/9.2/static/largeobjects.html>
>
> or something else? *[Sridevi] yes, internally it refers to large
> objects*.
>
>

***
> Expecting: pg_dump/pg_restore should work for minimum large data
> size <20GB.
>

***

>
> What data size are you talking about, the entire dump file or an
> object in the file?

***
*[Sridevi] I am talking about entire dump file
> size, which of size >3GB*

***

>
>
>
>
> PostgreSQL version number you are running: postgres92-9.2.9-1.x86_64
>
> How you installed PostgreSQL:
>
> Linux RHEL(Backup) installed using rpm.
>
> CentOS7.2(Restore) installed using yum.
>
> Operating system and version:
>
> Backup - Red Hat Enterprise Linux Server release 5.4 (Tikanga)
>
> Restore -centos-release-7-2.1511.el7.centos.2.10.x86_64
>
>
> What program you're using to connect to PostgreSQL:
> pg_dump/pg_restore
> using shell script
>
>

***
> What are the scripts? *[Sridevi]* - *We are using Linux scripts,
> which starts/stops application process during the postgres
> backup/restore process. And also scripts takes care of additional
> details specific to application. These scripts internally invoke
> postgres processes for backup and restore.
> *

****
>
>
> Is there anything relevant or unusual in the PostgreSQL server
> logs?:
>
> Pg_dump verbose log: stuck after: pg_dump: saving
> large objects
>
> Pg_restore verbose log: Stuck after:
> pg_restore:
> restoring large objects
>
> Some times: pg_restore: pg_restore:
> processing item 4376515 BLOB 4993394
>
> pg_restore:
> executing BLOB
> 4993394
>
> For questions about any kind of error:
>
>
>
> What you were doing when the error happened / how to cause the
> error:
> Tried options pg_dump using split and restore. Still same issue
> exists.
>
>

> Explain split and restore?
***

>
> *[Sridevi]* Split option of pg_dump, splits dump file into multiple
> files based on size and restore will combine all files and restore the
> data.
> I am referring to below link for split and restore.
>
> http://www.postgresql-archive.org/large-database-problems-with-pg-dump-and-pg-restore-td3236910.html
>
> I tried below commands:
> *Backup:* /opt/postgres/9.2/bin/pg_dump -v -c -h localhost -p 5432 -U
> ${$db_user}-w -Fc ${db_name}- | split -b 1000m -
> /opt/backups/${dump_file_name}
> *Restore: *cat /opt/backups/${dump_file_name}* |
> /opt/postgres/9.2/bin/pg_restore | /opt/postgres/9.2/bin/psql
> ${db_name}-h localhost -p 5432 -v -U ${$db_user} -w
> The restore is getting stuck at below error message and process gets
> terminated.
> could not send data to client: Broken pipe
> connection to client lost

***

So all this happening on the same host, correct?

I do not see anything that is large object specific in the error above.

What is the error message you get at the terminal when you do not use
the split/cat method?

Have you checked the ulimit settings as suggested by Tom Lane?

>
> The EXACT TEXT of the error message you're getting, if there is one:
> (Copy and paste the message to the email, do not send a screenshot)
>
> - No specific error, pg_dump/pg_restore getting terminated for
> data >2GB
>
>
> Regards,
>
> Sridevi
>
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com