Select query regarding info

["Yogesh. Sharma" <Yogesh1.Sharma@xxxxxxxxxxxxxxxxxx>]

HI Everyone,

 

Below DB query is showing below error on postgresql9.3.

SELECT '\'' || t2.name || '\'', '\'' || t1.phone_number || '\'', '\'' || t1.details || '\'', '\'' || t1.description || '\'', '\'' || (CASE WHEN t1.s_id IS NULL THEN 'N/A' ELSE t3.s_type END) || '\'', '\'' || t1.s_id || '\'' FROM abc_tble AS t1 LEFT JOIN pqrtable AS t2 ON t1.s_id = nid LEFT JOIN te AS t3 ON t1.s_id = t3.s_id;

Invalid command \''. Try \? for help.

But Above query is working fine in postgresql8.3.

Solution is provided by someone:-

The SQL standard defines two single quotes to escape one inside a literal: ''''

Postgres 8.3 defaulted to a non-standard behavior where it was allowed to escape a single quote using a backslash: '\''

This deviation from the SQL standard was always discouraged and can be controlled through the configuration parameter standard_conforming_strings

With version 9.1 the default for this parameter was changed from off to on. Version 8.1 and later would emit a warning when you used the non-standard way of escaping single quotes (unless you explicitly turned that off)

 

 

Could you please provide below information.

 How to change standard_conforming_strings value of postgresql.conf? I have checked but this option is not found in postgresql.conf.

Because according to this option, below query is failed.

 

Regards,

Yogesh

 

From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Xavier 12
Sent: Thursday, June 18, 2015 12:47 PM
To: Sameer Kumar; pgsql-general@xxxxxxxxxxxxxx
Subject: Re: [GENERAL] pg_xlog on a hot_stanby slave

 

 

On 18/06/2015 04:00, Sameer Kumar wrote:

 

On Wed, 17 Jun 2015 15:24 Xavier 12 <maniatux@xxxxxxxxx> wrote:

On 17/06/2015 03:17, Sameer Kumar wrote:

On Tue, 16 Jun 2015 16:55 Xavier 12 <maniatux@xxxxxxxxx> wrote:

Hi everyone,

Questions about pg_xlogs again...
I have two Postgresql 9.1 servers in a master/slave stream replication
(hot_standby).

Psql01 (master) is backuped with Barman and pg_xlogs is correctly
purged (archive_command is used).

Hower, Psql02 (slave) has a huge pg_xlog (951 files, 15G for 7 days
only, it keeps growing up until disk space is full). I have found
documentation and tutorials, mailing list, but I don't know what is
suitable for a Slave. Leads I've found :

- checkpoints
- archive_command
- archive_cleanup

Master postgresq.conf :

[...]
wal_level = 'hot_standby'
archive_mode = on
archive_command = 'rsync -az /var/lib/postgresql/9.1/main/pg_xlog/%f
barman@xxxxxxx:/data/pgbarman/psql01/incoming/%f'
max_wal_senders = 5
wal_keep_segments = 64

What's this parameter's value on Slave?

Hm... You have a point.
That autovacuum parameter seems to be useless on a slave.
I'll try to remove it and check pg_xlog.

That was not my point. I was actually asking about wal_keep_segment. Nevermind I found that I had misses the info (found it below. Please see my response).
Besides I try to keep my master and standby config as same as possible(so my advise ia to not switchoff autovacuum). The parameters which are imeffective on slave anyways won't have an effect. Same goes for parameters on master.
This helps me when I swap roles or do a failover. I have less parameters to be worried about.

Okay

Can you check the pg_log for log files. They may have se info? I am sorry if you have already provided that info (after I finish I will try to look at your previous emails on this thread)

Nothing...
/var/log/postgresql/postgresql-2015-06-17_111131.log is empty (except old messages at the begining related to a configuration issue - which is now solved - after rebuilding the cluster yesterday).
/var/log/syslog has nothing but these :

Jun 18 09:10:11 Bdd02 postgres[28400]: [2-1] 2015-06-18 09:10:11 CEST LOG:  paquet de d?marrage incomplet
Jun 18 09:10:41 Bdd02 postgres[28523]: [2-1] 2015-06-18 09:10:41 CEST LOG:  paquet de d?marrage incomplet
Jun 18 09:11:11 Bdd02 postgres[28557]: [2-1] 2015-06-18 09:11:11 CEST LOG:  paquet de d?marrage incomplet
Jun 18 09:11:41 Bdd02 postgres[28652]: [2-1] 2015-06-18 09:11:41 CEST LOG:  paquet de d?marrage incomplet
Jun 18 09:12:11 Bdd02 postgres[28752]: [2-1] 2015-06-18 09:12:11 CEST LOG:  paquet de d?marrage incomplet
Jun 18 09:12:41 Bdd02 postgres[28862]: [2-1] 2015-06-18 09:12:41 CEST LOG:  paquet de d?marrage incomplet
Jun 18 09:13:11 Bdd02 postgres[28891]: [2-1] 2015-06-18 09:13:11 CEST LOG:  paquet de d?marrage incomplet
Jun 18 09:13:40 Bdd02 postgres[28987]: [2-1] 2015-06-18 09:13:40 CEST LOG:  paquet de d?marrage incomplet

These messages are related to Zabbix (psql port check).

Also can you share the vacuum cost parameters in your environment?

I don't understand that part... is this in postgresql.conf ?

autovacuum = on

Slave postgresql.conf :

[...]
wal_level = minimal
wal_keep_segments = 32

Sorry I missed this somehow earlier. Any reason why you think you need to retain 32 wal files on slave?

No but I get the feeling that the parameter is ignored by my slave... should I try another value ?

hot_standby = on

Slave recovery.conf :

standby_mode = 'on'
primary_conninfo = 'host=10.0.0.1 port=5400 user=postgres'
trigger_file = '/var/lib/postgresql/9.1/triggersql'
restore_command='cp /var/lib/postgresql/9.1/wal_archive/%f "%p"'
archive_cleanup_command =
'/usr/lib/postgresql/9.1/bin/pg_archivecleanup
/var/lib/postgresql/9.1/wal_archive/ %r'

Also consider setting hot_standby_feesback to on.

I will check that parameter in the documentation,

Thanks

How can I reduce the number of WAL files on the hot_stanby slave ?

Thanks

Regards.

Xavier C.

--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

 

 

 

 

DISCLAIMER:
-----------------------------------------------------------------------------------------------------------------------
The contents of this e-mail and any attachment(s) are confidential and
intended
for the named recipient(s) only. 
It shall not attach any liability on the originator or NEC or its
affiliates. Any views or opinions presented in 
this email are solely those of the author and may not necessarily reflect the
opinions of NEC or its affiliates. 
Any form of reproduction, dissemination, copying, disclosure, modification,
distribution and / or publication of 
this message without the prior written consent of the author of this e-mail is
strictly prohibited. If you have 
received this email in error please delete it and notify the sender
immediately. .
-----------------------------------------------------------------------------------------------------------------------