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.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>> 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.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
