Re: [GENERAL] PostgreSQL 9.2.4 + CentOS 6.5 64 bit - segfault error in initdb
I do not have any earlier versions of postgres installed, neither a parallel instance running. In the pgstartup.log file, only the segfault error is recorded, nothing else. I have downloaded the following RPMs from http://yum.postgresql.org/9.2/redhat/rhel-5.6-x86_64/ postgresql92-9.2.4-1PGDG.rhel5.x86_64.rpm postgresql92-libs-9.2.4-1PGDG.rhel5.x86_64.rpm postgresql92-contrib-9.2.4-1PGDG.rhel5.x86_64.rpm postgresql92-server-9.2.4-1PGDG.rhel5.x86_64.rpm A core file was not generated. I will try with adding the ulimit command to the service script. Attached is the service script. Thanks Bhushan Pathak On Fri, Jun 6, 2014 at 8:09 PM, Tom Lane t...@sss.pgh.pa.us wrote: Bhushan Pathak bhushan.patha...@gmail.com writes: Stopping postgresql service: [ OK ] Starting postgresql service: [FAILED] pgstartup log has the same line - Segmentation fault (core dumped) Where is this core dump file generated? How do we proceed further from here? FWIW, I'd expect any such core to be generated either in postgres' home directory or the $PGDATA directory, depending on what is failing when. If you don't see a core, it's likely because the failing program is running under ulimit -c 0, which is the default environment for daemons (for security reasons). Try adding ulimit -c unlimited to the start script and/or the postgres user's ~/.bash_profile to see if you can get a core file for debugging. The issue seems like it must trace back to some difference in the normal shell environment of the postgres user versus the environment set up by service ... but it's not clear yet what that difference is. Also, it's not very clear whether you're trying to use the Red Hat/CentOS packaging of PG, or the PGDG packaging. As Adrian alluded to, those are not terribly compatible --- if you've got fragments of both laying about, that could be causing issues. regards, tom lane postgresql-9.2 Description: Binary data -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL 9.2.4 + CentOS 6.5 64 bit - segfault error in initdb
Hi, On Mon, 2014-06-09 at 14:23 +0530, Bhushan Pathak wrote: A core file was not generated. Can you please also install the -debuginfo package? Regards, -- Devrim GÜNDÜZ Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer Twitter: @DevrimGunduz , @DevrimGunduzTR signature.asc Description: This is a digitally signed message part
Re: [GENERAL] PostgreSQL 9.2.4 + CentOS 6.5 64 bit - segfault error in initdb
Trying to figure out shell environment differences between the service script postgres shell, if I comment out the following variable in profile file/add them after performing initdb operation, the postgresql operations seem to work fine - PATH=$PATH:/usr/pgsql-9.2/bin export PATH export POSTGRESQL_JDBC_DRIVER=/opt/lib/postgresql-9.2-1003.jdbc4.jar export PG_HOME=/var/lib/pgsql/data/ These variables are needed by a few cron scripts that would be installed later on after my postgres config completes successfully. The yum postgresql link I used earlier to download the RPM dos not the debuginfo package for 9.2.4 version. Will it be OK to use the latest debuginfo package with the rest being on version 9.2.4? Or I will have to use all the latest RPM packages? Thanks Bhushan Pathak On Mon, Jun 9, 2014 at 3:34 PM, Devrim Gündüz dev...@gunduz.org wrote: Hi, On Mon, 2014-06-09 at 14:23 +0530, Bhushan Pathak wrote: A core file was not generated. Can you please also install the -debuginfo package? Regards, -- Devrim GÜNDÜZ Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer Twitter: @DevrimGunduz , @DevrimGunduzTR
[GENERAL] pg_standby replication problem
Please help me with this, my secondary server shows a replication problem. It stopped at the file called *00054BAF00AF** …*then from here primary server kept on sending walfiles, until the walfiles used up the disc space in the data directory. How do I fix this problem. It’s postgres 9.1.2. *Postgres log file Postgres-2014-06-08_00.log file **has the following details :* 2014-06-08 00:15:54 SAST LOG: restored log file *00054BAF00AF from* archive Trigger file: /tmp/recovery.pgsql.trigger.5432 Waiting for WAL file: 00054BAF00B0 WAL file path:/pgsql2/walfiles/00054BAF00B0 Restoring to: pg_xlog/RECOVERYXLOG Sleep interval: 2 seconds Max wait interval:0 forever *Command for restore: cp /pgsql2/walfiles/00054BAF00B0 pg_xlog/RECOVERYXLOG* Keep archive history: 00054BAE00F7 and later WAL file not present yet. Checking for trigger file... WAL file not present yet. Checking for trigger file... WAL file not present yet. Checking for trigger file... WAL file not present yet. Checking for trigger file... WAL file not present yet. Checking for trigger file... CONFIDENTIALITY NOTICE The contents of and attachments to this e-mail are intended for the addressee only, and may contain the confidential information of Argility (Proprietary) Limited and/or its subsidiaries. Any review, use or dissemination thereof by anyone other than the intended addressee is prohibited.If you are not the intended addressee please notify the writer immediately and destroy the e-mail. Argility (Proprietary) Limited and its subsidiaries distance themselves from and accept no liability for unauthorised use of their e-mail facilities or e-mails sent other than strictly for business purposes.
Re: [GENERAL] pg_standby replication problem
FilesystemSize Used Avail Use% Mounted on /dev/sda3 57G 15G 39G 28% / /dev/mapper/vg0-pgsql2 5.4T 5.3T 0 100% /pgsql2 /dev/sda1 99M 12M 83M 13% /boot tmpfs 30G 0 30G 0% /dev/shm *Disc space Breakdown:* 4.0K./backup 12K ./copy 4.9T./data 204K./test 16K ./lost+found 361G./walfiles 5.3T. *From:* Khangelani Gama [mailto:kg...@argility.com] *Sent:* Monday, June 09, 2014 1:42 PM *To:* pgsql-general@postgresql.org *Subject:* pg_standby replication problem Please help me with this, my secondary server shows a replication problem. It stopped at the file called *00054BAF00AF …*then from here primary server kept on sending walfiles, until the walfiles used up the disc space in the data directory. How do I fix this problem. It’s postgres 9.1.2. *Postgres log file Postgres-2014-06-08_00.log file **has the following details :* 2014-06-08 00:15:54 SAST LOG: restored log file *00054BAF00AF from* archive Trigger file: /tmp/recovery.pgsql.trigger.5432 Waiting for WAL file: 00054BAF00B0 WAL file path:/pgsql2/walfiles/00054BAF00B0 Restoring to: pg_xlog/RECOVERYXLOG Sleep interval: 2 seconds Max wait interval:0 forever *Command for restore: cp /pgsql2/walfiles/00054BAF00B0 pg_xlog/RECOVERYXLOG* Keep archive history: 00054BAE00F7 and later WAL file not present yet. Checking for trigger file... WAL file not present yet. Checking for trigger file... WAL file not present yet. Checking for trigger file... WAL file not present yet. Checking for trigger file... WAL file not present yet. Checking for trigger file... CONFIDENTIALITY NOTICE The contents of and attachments to this e-mail are intended for the addressee only, and may contain the confidential information of Argility (Proprietary) Limited and/or its subsidiaries. Any review, use or dissemination thereof by anyone other than the intended addressee is prohibited.If you are not the intended addressee please notify the writer immediately and destroy the e-mail. Argility (Proprietary) Limited and its subsidiaries distance themselves from and accept no liability for unauthorised use of their e-mail facilities or e-mails sent other than strictly for business purposes.
Re: [GENERAL] pg_standby replication problem
The big question we can’t answer is that when the replication was at this point (*Command for restore: cp /pgsql2/walfiles/00054BAF00B0 pg_xlog/RECOVERYXLOG* ) , it then started to say WAL file not present yet. We can’t find this *00054BAF00B0 *file any where* . * *Command for restore: cp /pgsql2/walfiles/00054BAF00B0 pg_xlog/RECOVERYXLOG* Keep archive history: 00054BAE00F7 and later WAL file not present yet. Checking for trigger file... -rw--- 1 postgres postgres 16M Jun 7 21:42 00054BAE00F7 *From:* Khangelani Gama [mailto:kg...@argility.com] *Sent:* Monday, June 09, 2014 1:45 PM *To:* pgsql-general@postgresql.org *Subject:* RE: pg_standby replication problem FilesystemSize Used Avail Use% Mounted on /dev/sda3 57G 15G 39G 28% / /dev/mapper/vg0-pgsql2 5.4T 5.3T 0 100% /pgsql2 /dev/sda1 99M 12M 83M 13% /boot tmpfs 30G 0 30G 0% /dev/shm *Disc space Breakdown:* 4.0K./backup 12K ./copy 4.9T./data 204K./test 16K ./lost+found 361G./walfiles 5.3T. *From:* Khangelani Gama [mailto:kg...@argility.com kg...@argility.com] *Sent:* Monday, June 09, 2014 1:42 PM *To:* pgsql-general@postgresql.org *Subject:* pg_standby replication problem Please help me with this, my secondary server shows a replication problem. It stopped at the file called *00054BAF00AF …*then from here primary server kept on sending walfiles, until the walfiles used up the disc space in the data directory. How do I fix this problem. It’s postgres 9.1.2. *Postgres log file Postgres-2014-06-08_00.log file **has the following details :* 2014-06-08 00:15:54 SAST LOG: restored log file *00054BAF00AF from* archive Trigger file: /tmp/recovery.pgsql.trigger.5432 Waiting for WAL file: 00054BAF00B0 WAL file path:/pgsql2/walfiles/00054BAF00B0 Restoring to: pg_xlog/RECOVERYXLOG Sleep interval: 2 seconds Max wait interval:0 forever *Command for restore: cp /pgsql2/walfiles/00054BAF00B0 pg_xlog/RECOVERYXLOG* Keep archive history: 00054BAE00F7 and later WAL file not present yet. Checking for trigger file... WAL file not present yet. Checking for trigger file... WAL file not present yet. Checking for trigger file... WAL file not present yet. Checking for trigger file... WAL file not present yet. Checking for trigger file... CONFIDENTIALITY NOTICE The contents of and attachments to this e-mail are intended for the addressee only, and may contain the confidential information of Argility (Proprietary) Limited and/or its subsidiaries. Any review, use or dissemination thereof by anyone other than the intended addressee is prohibited.If you are not the intended addressee please notify the writer immediately and destroy the e-mail. Argility (Proprietary) Limited and its subsidiaries distance themselves from and accept no liability for unauthorised use of their e-mail facilities or e-mails sent other than strictly for business purposes.
Re: [GENERAL] PostgreSQL 9.2.4 + CentOS 6.5 64 bit - segfault error in initdb
On 06/09/2014 04:02 AM, Bhushan Pathak wrote: Trying to figure out shell environment differences between the service script postgres shell, if I comment out the following variable in profile file/add them after performing initdb operation, the postgresql operations seem to work fine - In the init script the only thing that is exported that could possibly conflict is PGDATA which is the same as your PG_HOME, but they are assigned to different variables so I am not sure where the conflict would occur. PATH=$PATH:/usr/pgsql-9.2/bin export PATH This seems alright, though not needed by the script as it uses the full path when calling the binaries. export POSTGRESQL_JDBC_DRIVER=/opt/lib/postgresql-9.2-1003.jdbc4.jar Not sure how the above would have any effect with the initdb as Java is not involved. export PG_HOME=/var/lib/pgsql/data/ This is redundant, I would just use the PGDATA environment variable exported by the script. These variables are needed by a few cron scripts that would be installed later on after my postgres config completes successfully. The yum postgresql link I used earlier to download the RPM dos not the debuginfo package for 9.2.4 version. Will it be OK to use the latest debuginfo package with the rest being on version 9.2.4? Or I will have to use all the latest RPM packages? Thanks Bhushan Pathak -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_standby replication problem
Please please help *From:* Khangelani Gama [mailto:kg...@argility.com] *Sent:* Monday, June 09, 2014 1:42 PM *To:* pgsql-general@postgresql.org *Subject:* pg_standby replication problem Please help me with this, my secondary server shows a replication problem. It stopped at the file called *00054BAF00AF …*then from here primary server kept on sending walfiles, until the walfiles used up the disc space in the data directory. How do I fix this problem. It’s postgres 9.1.2. *Postgres log file Postgres-2014-06-08_00.log file **has the following details :* 2014-06-08 00:15:54 SAST LOG: restored log file *00054BAF00AF from* archive Trigger file: /tmp/recovery.pgsql.trigger.5432 Waiting for WAL file: 00054BAF00B0 WAL file path:/pgsql2/walfiles/00054BAF00B0 Restoring to: pg_xlog/RECOVERYXLOG Sleep interval: 2 seconds Max wait interval:0 forever *Command for restore: cp /pgsql2/walfiles/00054BAF00B0 pg_xlog/RECOVERYXLOG* Keep archive history: 00054BAE00F7 and later WAL file not present yet. Checking for trigger file... WAL file not present yet. Checking for trigger file... WAL file not present yet. Checking for trigger file... WAL file not present yet. Checking for trigger file... WAL file not present yet. Checking for trigger file... CONFIDENTIALITY NOTICE The contents of and attachments to this e-mail are intended for the addressee only, and may contain the confidential information of Argility (Proprietary) Limited and/or its subsidiaries. Any review, use or dissemination thereof by anyone other than the intended addressee is prohibited.If you are not the intended addressee please notify the writer immediately and destroy the e-mail. Argility (Proprietary) Limited and its subsidiaries distance themselves from and accept no liability for unauthorised use of their e-mail facilities or e-mails sent other than strictly for business purposes.
Re: [GENERAL] PostgreSQL 9.2.4 + CentOS 6.5 64 bit - segfault error in initdb
On 06/09/2014 01:53 AM, Bhushan Pathak wrote: I do not have any earlier versions of postgres installed, neither a parallel instance running. In the pgstartup.log file, only the segfault error is recorded, nothing else. I have downloaded the following RPMs from http://yum.postgresql.org/9.2/redhat/rhel-5.6-x86_64/ I thought you where on Centos 6.5 would you not need?: http://yum.postgresql.org/9.2/redhat/rhel-6-x86_64/repoview/ -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_standby replication problem
On 06/09/2014 07:28 AM, Khangelani Gama wrote: Please please help Before anyone can help you will need to provide more information on what your archiving, replication setup is. To begin: 1)Are you doing both archiving and streaming replication? 2) What are the settings in the configuration files for those operations? 3) What is the layout for archiving, in other words do the archived files get copied remotely to a third site or some other arrangement? 4) What caused the trigger file to be set? *From:*Khangelani Gama [mailto:kg...@argility.com mailto:kg...@argility.com] *Sent:* Monday, June 09, 2014 1:42 PM *To:* pgsql-general@postgresql.org mailto:pgsql-general@postgresql.org *Subject:* pg_standby replication problem Please help me with this, my secondary server shows a replication problem. It stopped at the file called *00054BAF00AF …*then from here primary server kept on sending walfiles, until the walfiles used up the disc space in the data directory. How do I fix this problem. It’s postgres 9.1.2. *_Postgres log file Postgres-2014-06-08_00.log file _*_has the following details :_ 2014-06-08 00:15:54 SAST LOG: restored log file *00054BAF00AF from*archive Trigger file: /tmp/recovery.pgsql.trigger.5432 Waiting for WAL file: 00054BAF00B0 WAL file path:/pgsql2/walfiles/00054BAF00B0 Restoring to: pg_xlog/RECOVERYXLOG Sleep interval: 2 seconds Max wait interval:0 forever *Command for restore: cp /pgsql2/walfiles/00054BAF00B0 pg_xlog/RECOVERYXLOG* Keep archive history: 00054BAE00F7 and later WAL file not present yet. Checking for trigger file... WAL file not present yet. Checking for trigger file... WAL file not present yet. Checking for trigger file... WAL file not present yet. Checking for trigger file... WAL file not present yet. Checking for trigger file... CONFIDENTIALITY NOTICE The contents of and attachments to this e-mail are intended for the addressee only, and may contain the confidential information of Argility (Proprietary) Limited and/or its subsidiaries. Any review, use or dissemination thereof by anyone other than the intended addressee is prohibited.If you are not the intended addressee please notify the writer immediately and destroy the e-mail. Argility (Proprietary) Limited and its subsidiaries distance themselves from and accept no liability for unauthorised use of their e-mail facilities or e-mails sent other than strictly for business purposes. -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_standby replication problem
On Monday, June 09, 2014 04:28:53 PM Khangelani Gama wrote: Please help me with this, my secondary server shows a replication problem. It stopped at the file called *00054BAF00AF …*then from here primary server kept on sending walfiles, until the walfiles used up the disc space in the data directory. How do I fix this problem. It’s postgres 9.1.2. It looks to me like your archive_command is probably failing on the primary server. If that fails, the logs will build up and fill up your disk as described. And they wouldn't be available to the slave to find. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_standby replication problem
-Original Message-
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Alan Hodgson
Sent: Monday, June 09, 2014 4:51 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] pg_standby replication problem
On Monday, June 09, 2014 04:28:53 PM Khangelani Gama wrote:
Please help me with this, my secondary server shows a replication problem.
It stopped at the file called *00054BAF00AF …*then from
here primary server kept on sending walfiles, until the walfiles used
up the disc space in the data directory. How do I fix this problem.
It’s postgres 9.1.2.
It looks to me like your archive_command is probably failing on the primary
server. If that fails, the logs will build up and fill up your disk as
described. And they wouldn't be available to the slave to find.
I am sorry, I am still trying to understand all the settings, the person who
set up the servers left the company.
In primary server, postgresql.conf shows the following:
# WRITE AHEAD LOG
#--
# - Settings -
wal_level = archive
# - Checkpoints -
checkpoint_segments = 128
checkpoint_timeout = 15min
checkpoint_warning = 885s
# - Archiving -
archive_mode = on
#archive_mode = off # allows archiving to be done
archive_command = '/home/cdbs/bin/run_replication.sh %p %f'
# REPLICATION
#--
# - Master Server -
# These settings are ignored on a standby server
max_wal_senders = 3
The setting archive_command points to a script being run and the variable %p
and %f being passed.
There is replication script running in the primary server has the
following:
while [ $test = false ]
do
rsync -a /pgsql2/data/${src}
postgres@10.58.101.10:/pgsql2/walfiles/${dest}
/tmp/run_replication.sh.out 2 /tmp/run_replication.sh.out
test=`ssh AB_CDS3 if [ -f /pgsql2/walfiles/${dest} ];then echo
'true' ;else echo 'false';fi`
if [ ${test} = false ]
then
echo Test is false for CDS3, sleeping 10
/tmp/run_replication.sh.out
sleep 10
cnt=$(( $cnt + 1 ))
if [ ${cnt} -ge 60 ]
then
message=Replication ERROR: Unable to send WAL
file(${desc}) from CDS to CDS3
echo `date` : ${message}
/tmp/run_replication.sh.out
sendsms
fi
fi
done
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make
changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
CONFIDENTIALITY NOTICE
The contents of and attachments to this e-mail are intended for the addressee
only, and may contain the confidential
information of Argility (Proprietary) Limited and/or its subsidiaries. Any
review, use or dissemination thereof by anyone
other than the intended addressee is prohibited.If you are not the intended
addressee please notify the writer immediately
and destroy the e-mail. Argility (Proprietary) Limited and its subsidiaries
distance themselves from and accept no liability
for unauthorised use of their e-mail facilities or e-mails sent other than
strictly for business purposes.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_standby replication problem
I just saw got this from the primary server (/tmp/run_replication.sh.out), secondary server's IP 10.58.101.10. replication started: Sun Jun 8 00:05:26 SAST 2014 source: pg_xlog/00054BAF00AF, dest: 00054BAF00AF replication finished: Sun Jun 8 00:05:33 SAST 2014 replication started: Sun Jun 8 00:05:33 SAST 2014 source: pg_xlog/00054BAF00B0, dest: 00054BAF00B0 ssh: connect to host 10.58.101.10 port 22: Connection timed out^M rsync: connection unexpectedly closed (0 bytes received so far) [sender] rsync error: unexplained error (code 255) at io.c(600) [sender=3.0.6] replication finished: Sun Jun 8 00:07:41 SAST 2014 replication started: Sun Jun 8 00:07:41 SAST 2014 source: pg_xlog/00054BAF00B1, dest: 00054BAF00B1 replication finished: Sun Jun 8 00:07:53 SAST 2014 replication started: Sun Jun 8 00:07:53 SAST 2014 source: pg_xlog/00054BAF00B2, dest: 00054BAF00B2 replication finished: Sun Jun 8 00:07:57 SAST 2014 replication started: Sun Jun 8 00:07:58 SAST 2014 source: pg_xlog/00054BAF00B3, dest: 00054BAF00B3 replication finished: Sun Jun 8 00:08:06 SAST 2014 replication started: Sun Jun 8 00:08:06 SAST 2014 source: pg_xlog/00054BAF00B4, dest: 00054BAF00B4 replication finished: Sun Jun 8 00:08:11 SAST 2014 replication started: Sun Jun 8 00:08:11 SAST 2014 source: pg_xlog/00054BAF00B5, dest: 00054BAF00B5 replication finished: Sun Jun 8 00:08:16 SAST 2014 replication started: Sun Jun 8 00:08:16 SAST 2014 source: pg_xlog/00054BAF00B6, dest: 00054BAF00B6 replication finished: Sun Jun 8 00:08:22 SAST 2014 CONFIDENTIALITY NOTICE The contents of and attachments to this e-mail are intended for the addressee only, and may contain the confidential information of Argility (Proprietary) Limited and/or its subsidiaries. Any review, use or dissemination thereof by anyone other than the intended addressee is prohibited.If you are not the intended addressee please notify the writer immediately and destroy the e-mail. Argility (Proprietary) Limited and its subsidiaries distance themselves from and accept no liability for unauthorised use of their e-mail facilities or e-mails sent other than strictly for business purposes. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_standby replication problem
-Original Message- From: Khangelani Gama [mailto:kg...@argility.com] Sent: Monday, June 09, 2014 5:26 PM To: 'Alan Hodgson'; 'pgsql-general@postgresql.org' Subject: RE: [GENERAL] pg_standby replication problem I just saw got this from the primary server (/tmp/run_replication.sh.out), secondary server's IP 10.58.101.10. replication started: Sun Jun 8 00:05:26 SAST 2014 source: pg_xlog/00054BAF00AF, dest: 00054BAF00AF replication finished: Sun Jun 8 00:05:33 SAST 2014 replication started: Sun Jun 8 00:05:33 SAST 2014 source: pg_xlog/00054BAF00B0, dest: 00054BAF00B0 ssh: connect to host 10.58.101.10 port 22: Connection timed out^M rsync: connection unexpectedly closed (0 bytes received so far) [sender] rsync error: unexplained error (code 255) at io.c(600) [sender=3.0.6] replication finished: Sun Jun 8 00:07:41 SAST 2014 replication started: Sun Jun 8 00:07:41 SAST 2014 source: pg_xlog/00054BAF00B1, dest: 00054BAF00B1 replication finished: Sun Jun 8 00:07:53 SAST 2014 replication started: Sun Jun 8 00:07:53 SAST 2014 source: pg_xlog/00054BAF00B2, dest: 00054BAF00B2 replication finished: Sun Jun 8 00:07:57 SAST 2014 replication started: Sun Jun 8 00:07:58 SAST 2014 source: pg_xlog/00054BAF00B3, dest: 00054BAF00B3 replication finished: Sun Jun 8 00:08:06 SAST 2014 replication started: Sun Jun 8 00:08:06 SAST 2014 source: pg_xlog/00054BAF00B4, dest: 00054BAF00B4 replication finished: Sun Jun 8 00:08:11 SAST 2014 replication started: Sun Jun 8 00:08:11 SAST 2014 source: pg_xlog/00054BAF00B5, dest: 00054BAF00B5 replication finished: Sun Jun 8 00:08:16 SAST 2014 replication started: Sun Jun 8 00:08:16 SAST 2014 source: pg_xlog/00054BAF00B6, dest: 00054BAF00B6 replication finished: Sun Jun 8 00:08:22 SAST 2014 Since there was a Connection time out Problem in the primary server, how can I make disc space in the secondary server for the replication to continue from where it stopped. Do I remove waltfiles from the secondary server? Disc space Breakdown: 4.0K./backup 12K ./copy 4.9T./data 204K./test 16K ./lost+found 361G./walfiles 5.3T. CONFIDENTIALITY NOTICE The contents of and attachments to this e-mail are intended for the addressee only, and may contain the confidential information of Argility (Proprietary) Limited and/or its subsidiaries. Any review, use or dissemination thereof by anyone other than the intended addressee is prohibited.If you are not the intended addressee please notify the writer immediately and destroy the e-mail. Argility (Proprietary) Limited and its subsidiaries distance themselves from and accept no liability for unauthorised use of their e-mail facilities or e-mails sent other than strictly for business purposes. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pg_standby replication problem
Hi All I would like to re-post the problem we have. The secondary server ran out the disc space due the replication problem (Connection Time out).Since there was a Connection time out Problem in the primary server, how can I make disc space in the secondary server for the replication to continue from where it stopped. Do I remove walfiles from the secondary server? Below it's the details of the log file from the primary server: replication started: Sun Jun 8 00:05:26 SAST 2014 source: pg_xlog/00054BAF00AF, dest: 00054BAF00AF replication finished: Sun Jun 8 00:05:33 SAST 2014 replication started: Sun Jun 8 00:05:33 SAST 2014 source: pg_xlog/00054BAF00B0, dest: 00054BAF00B0 ssh: connect to host 10.58.101.10 port 22: Connection timed out^M rsync: connection unexpectedly closed (0 bytes received so far) [sender] rsync error: unexplained error (code 255) at io.c(600) [sender=3.0.6] replication finished: Sun Jun 8 00:07:41 SAST 2014 replication started: Sun Jun 8 00:07:41 SAST 2014 source: pg_xlog/00054BAF00B1, dest: 00054BAF00B1 replication finished: Sun Jun 8 00:07:53 SAST 2014 replication started: Sun Jun 8 00:07:53 SAST 2014 source: pg_xlog/00054BAF00B2, dest: 00054BAF00B2 replication finished: Sun Jun 8 00:07:57 SAST 2014 replication started: Sun Jun 8 00:07:58 SAST 2014 source: pg_xlog/00054BAF00B3, dest: 00054BAF00B3 replication finished: Sun Jun 8 00:08:06 SAST 2014 replication started: Sun Jun 8 00:08:06 SAST 2014 source: pg_xlog/00054BAF00B4, dest: 00054BAF00B4 replication finished: Sun Jun 8 00:08:11 SAST 2014 replication started: Sun Jun 8 00:08:11 SAST 2014 source: pg_xlog/00054BAF00B5, dest: 00054BAF00B5 replication finished: Sun Jun 8 00:08:16 SAST 2014 replication started: Sun Jun 8 00:08:16 SAST 2014 source: pg_xlog/00054BAF00B6, dest: 00054BAF00B6 replication finished: Sun Jun 8 00:08:22 SAST 2014 Disc space Breakdown: 4.0K./backup 12K ./copy 4.9T./data 204K./test 16K ./lost+found 361G./walfiles 5.3T. FilesystemSize Used Avail Use% Mounted on /dev/sda3 57G 15G 39G 28% / /dev/mapper/vg0-pgsql2 5.4T 5.3T 0 100% /pgsql2 /dev/sda1 99M 12M 83M 13% /boot tmpfs 30G 0 30G 0% /dev/shm CONFIDENTIALITY NOTICE The contents of and attachments to this e-mail are intended for the addressee only, and may contain the confidential information of Argility (Proprietary) Limited and/or its subsidiaries. Any review, use or dissemination thereof by anyone other than the intended addressee is prohibited.If you are not the intended addressee please notify the writer immediately and destroy the e-mail. Argility (Proprietary) Limited and its subsidiaries distance themselves from and accept no liability for unauthorised use of their e-mail facilities or e-mails sent other than strictly for business purposes. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_standby replication problem
This is the standby replication setting with archive_command sending the WALs from master to standby. -Original Message- From: Khangelani Gama [mailto:kg...@argility.com] Sent: Monday, June 09, 2014 8:06 PM To: pgsql-general@postgresql.org Subject: pg_standby replication problem Hi All I would like to re-post the problem we have. The secondary server ran out the disc space due the replication problem (Connection Time out).Since there was a Connection time out Problem in the primary server, how can I make disc space in the secondary server for the replication to continue from where it stopped. Do I remove walfiles from the secondary server? Below it's the details of the log file from the primary server: replication started: Sun Jun 8 00:05:26 SAST 2014 source: pg_xlog/00054BAF00AF, dest: 00054BAF00AF replication finished: Sun Jun 8 00:05:33 SAST 2014 replication started: Sun Jun 8 00:05:33 SAST 2014 source: pg_xlog/00054BAF00B0, dest: 00054BAF00B0 ssh: connect to host 10.58.101.10 port 22: Connection timed out^M rsync: connection unexpectedly closed (0 bytes received so far) [sender] rsync error: unexplained error (code 255) at io.c(600) [sender=3.0.6] replication finished: Sun Jun 8 00:07:41 SAST 2014 replication started: Sun Jun 8 00:07:41 SAST 2014 source: pg_xlog/00054BAF00B1, dest: 00054BAF00B1 replication finished: Sun Jun 8 00:07:53 SAST 2014 replication started: Sun Jun 8 00:07:53 SAST 2014 source: pg_xlog/00054BAF00B2, dest: 00054BAF00B2 replication finished: Sun Jun 8 00:07:57 SAST 2014 replication started: Sun Jun 8 00:07:58 SAST 2014 source: pg_xlog/00054BAF00B3, dest: 00054BAF00B3 replication finished: Sun Jun 8 00:08:06 SAST 2014 replication started: Sun Jun 8 00:08:06 SAST 2014 source: pg_xlog/00054BAF00B4, dest: 00054BAF00B4 replication finished: Sun Jun 8 00:08:11 SAST 2014 replication started: Sun Jun 8 00:08:11 SAST 2014 source: pg_xlog/00054BAF00B5, dest: 00054BAF00B5 replication finished: Sun Jun 8 00:08:16 SAST 2014 replication started: Sun Jun 8 00:08:16 SAST 2014 source: pg_xlog/00054BAF00B6, dest: 00054BAF00B6 replication finished: Sun Jun 8 00:08:22 SAST 2014 Disc space Breakdown: 4.0K./backup 12K ./copy 4.9T./data 204K./test 16K ./lost+found 361G./walfiles 5.3T. FilesystemSize Used Avail Use% Mounted on /dev/sda3 57G 15G 39G 28% / /dev/mapper/vg0-pgsql2 5.4T 5.3T 0 100% /pgsql2 /dev/sda1 99M 12M 83M 13% /boot tmpfs 30G 0 30G 0% /dev/shm CONFIDENTIALITY NOTICE The contents of and attachments to this e-mail are intended for the addressee only, and may contain the confidential information of Argility (Proprietary) Limited and/or its subsidiaries. Any review, use or dissemination thereof by anyone other than the intended addressee is prohibited.If you are not the intended addressee please notify the writer immediately and destroy the e-mail. Argility (Proprietary) Limited and its subsidiaries distance themselves from and accept no liability for unauthorised use of their e-mail facilities or e-mails sent other than strictly for business purposes. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_standby replication problem
On Monday, June 09, 2014 08:05:41 PM Khangelani Gama wrote: Hi All I would like to re-post the problem we have. The secondary server ran out the disc space due the replication problem (Connection Time out). The secondary server would not (could not) run out of drive space due to a problem on the primary. You probably need to figure out why that server is out of drive space and fix it, and then I expect your replication problem will fix itself. If you do not have a process cleaning up old archived WAL files, and those are stored on the secondary, that could be the source of your problem. If you also have a separate networking issue (for the connection timeout), then you might need to fix that, too. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_standby replication problem
On 06/09/2014 11:15 AM, Khangelani Gama wrote: This is the standby replication setting with archive_command sending the WALs from master to standby. What are the conf settings on the standby server? -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] two questions about fulltext searchign / tsvector indexes
I'm having some issues with fulltext searching.
I've gone though the list archives and stack overflow, but can't seem to get
the exact answers. hoping someone can help.
Thanks in advance and apologies for these questions being rather basic. I just
felt the docs and some online posts are leading me into possibly making the
wrong decision and I want to make sure Im doing this right.
1. I need to make both 'title' and 'description' searchable. What is the
current proper way to index multiple columns of a table ( ie, not one ) ?
I've essentially seen the following in the docs, mailing list, and
various websites:
A unified index
CREATE INDEX CONCURRENTLY unified_tsvector_idx ON mytable USING
gin(to_tsvector('english', title || ' ' || description ));
Individual indexes
CREATE INDEX CONCURRENTLY title_tsvector_idx ON mytable USING
gin(to_tsvector('english', title ));
CREATE INDEX CONCURRENTLY description_tsvector_idx ON mytable
USING gin(to_tsvector('english', description ));
Using dedicated columns ( one or more )
ALTER TABLE
create trigger
I can't figure out which one to use. This is on a steadily growing
table of around 20MM rows that gets 20-80k new records a day, but existing
records are rarely updated.
2. I've been getting a handful of 'can not index words longer than 2047
characters' in my tests.
if this 2047 character max is on tokens, is there a way to lower it?
or to profile the index for distribution of tokens ? I don't think we have to
support any tokens larger than 20chars or so.
3a. What should EXPLAIN ANALYZE show if it is using the index ? i couldn't
find an example.
3b. Depending on how I index the column, what do I need to pass into the query
so that it uses the index ?
1. if the index is created like
gin(to_tsvector('english', title ));
do i have to search in this format ?
to_tsvector('english',title) @@ to_tsquery('english',
'dog') ;
2. if i use an index like
gin(to_tsvector('english', title || ' ' || description
));
what is the correct way to query the database and let the
planner know I want to use the index ?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_standby replication problem
-Original Message- From: Adrian Klaver [mailto:adrian.kla...@aklaver.com] Sent: Tuesday, June 10, 2014 1:42 AM To: Khangelani Gama; pgsql-general@postgresql.org Subject: Re: [GENERAL] pg_standby replication problem On 06/09/2014 11:15 AM, Khangelani Gama wrote: This is the standby replication setting with archive_command sending the WALs from master to standby. Thanks for feedback from everyone, I will try and remove the correct old walfiles. What are the conf settings on the standby server? Standby server config settings are as follows: # WRITE AHEAD LOG #-- # - Settings - wal_level = archive #wal_level = minimal# minimal, archive, or hot_standby # (change requires restart) #fsync = on # turns forced synchronization on or off synchronous_commit = off# synchronization level; on, off, or local #wal_sync_method = fsync # - Checkpoints - checkpoint_segments = 128 checkpoint_timeout = 15min checkpoint_warning = 885s #checkpoint_segments = 3 # - Archiving - archive_mode = on #archive_mode = off # allows archiving to be done # (change requires restart) # REPLICATION #-- # - Master Server - # These settings are ignored on a standby server max_wal_senders = 3 # max number of walsender processes # (change requires restart) # This is used when logging to stderr: logging_collector = on #logging_collector = off -- Adrian Klaver adrian.kla...@aklaver.com CONFIDENTIALITY NOTICE The contents of and attachments to this e-mail are intended for the addressee only, and may contain the confidential information of Argility (Proprietary) Limited and/or its subsidiaries. Any review, use or dissemination thereof by anyone other than the intended addressee is prohibited.If you are not the intended addressee please notify the writer immediately and destroy the e-mail. Argility (Proprietary) Limited and its subsidiaries distance themselves from and accept no liability for unauthorised use of their e-mail facilities or e-mails sent other than strictly for business purposes. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
