Re: [GENERAL] Select query regarding info

[Thomas Kellerer]

> Could you please provide below information.
> 
> How to change standard_conforming_strings value of postgresql.conf? 

I would not change that option. You should rather stick to standard conforming 
strings and fix your query. 
That can be done through a simple (and automated) search & replace.

Thomas



-- 
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] Select query regarding info

[Bill Moran]

On Thu, 18 Jun 2015 07:29:37 +
"Yogesh. Sharma"  wrote:

> 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.

Add it to the file.

Also, don't reply to unrelated threads with new questions, a lot of
people won't see your question if you do that, and if nobody sees
your question you won't get an answer.

-- 
Bill Moran


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

[GENERAL] Select query regarding info

[Yogesh. Sharma]

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-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Xavier 12
Sent: Thursday, June 18, 2015 12:47 PM
To: Sameer Kumar; pgsql-general@postgresql.org
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 
mailto:mania...@gmail.com>> wrote:

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

On Tue, 16 Jun 2015 16:55 Xavier 12 
mailto:mania...@gmail.com>> 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
bar...@nas.lan:/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_31.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_segme