[GENERAL] Slides for PGCon2016; "FTS is dead ? Long live FTS !"

[Andreas Joseph Krogh]

Hi.
 
Any news about when slides for $subject will be available?
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com 
 

Re: [GENERAL] Aborted pg_dump run empties existing archive file

[Jerry Sievers]

Ken Winter  writes:

> I just discovered that a run of pg_dump that gets aborted empties any 
> pre-existing backup file of the same name.  It happens whether the run was 
> deliberately canceled
> by the user or just failed because of a bad password (as in the example 
> below). 
>
> ~/dba$ pg_dump --host=localhost -U ken  --format=c 
> --file=mess_development.bak --no-owner --no-privileges mess_development
> Password: 
>
> ~/dba$ ls -l mess_development.bak
> -rw-rw-r-- 1 ken ken 87070 May 26 14:20 mess_development.bak
>
> ~/dba$ pg_dump --host=localhost -U ken  --format=c 
> --file=mess_development.bak --no-owner --no-privileges mess_development
> Password: 
> pg_dump: [archiver (db)] connection to database "mess_development" failed: 
> FATAL:  password authentication failed for user "ken"
> FATAL:  password authentication failed for user "ken"
>  
> ~/dba$ ls -l mess_development.bak
> -rw-rw-r-- 1 ken ken 0 May 26 14:21 mess_development.bak
>
> ~/dba$
>
> This is troubling because I can imagine a disaster scenario where one loses 
> one's previous backup and, perhaps because of a forgotten password, can't 
> generate a new
> one.  (This scenario hasn't happened to me yet, but seems like it could.)  
> In general, a failed run of a piece of software should leave everything 
> unchanged.  So maybe
> this is a bug that needs to be fixed?

Fix it by not overwriting your existing backup each time.

Yes, it could probably not trunc the file until at least getting a valid
DB connection so this aspect could probably be improved.

You are running a pretty old Pg version.  I vaguely remember discussion
about pg_dump or some other utility truncing files a bit prematurely but
it was long ago.

But it can't know beyond that point if something else is going to cause
the backup to abort.

And I would not expect pg_dump to create a new file alongside your old
file just in case since this can so easily be implemented in whatever
framework you are launching pg_dump with.

HTH



> ~ Ken
>

-- 
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consult...@comcast.net
p: 312.241.7800


-- 
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] Aborted pg_dump run empties existing archive file

[Melvin Davidson]

On Thu, May 26, 2016 at 2:48 PM, Ken Winter  wrote:

> I just discovered that a run of pg_dump that gets aborted empties any
> pre-existing backup file of the same name.  It happens whether the run was
> deliberately canceled by the user or just failed because of a bad password
> (as in the example below).
>
> ~/dba$ pg_dump --host=localhost -U ken  --format=c
> --file=mess_development.bak --no-owner --no-privileges mess_development
> Password: 
>
> ~/dba$ ls -l mess_development.bak
> -rw-rw-r-- 1 ken ken *87070* May 26 14:20 mess_development.bak
>
> ~/dba$ pg_dump --host=localhost -U ken  --format=c
> --file=mess_development.bak --no-owner --no-privileges mess_development
> Password: 
> pg_dump: [archiver (db)] connection to database "mess_development" failed:
> FATAL:  password authentication failed for user "ken"
> FATAL:  password authentication failed for user "ken"
>
> ~/dba$ ls -l mess_development.bak
> -rw-rw-r-- 1 ken ken *0* May 26 14:21 mess_development.bak
>
> ~/dba$
>
> This is troubling because I can imagine a disaster scenario where one
> loses one's previous backup and, perhaps because of a forgotten password,
> can't generate a new one.  (This scenario hasn't happened to *me* yet,
> but seems like it could.)  In general, a failed run of a piece of software
> should leave everything unchanged.  So maybe this is a bug that needs to be
> fixed?
>
> ~ Ken
>
>
>
>
>
>...a run of pg_dump that gets aborted empties any pre-existing backup file
of the same name

This is standard behavior. By using the same output file name, you are
telling the O/S to overwrite/delete that file and replace with new
information.
Most DBA's usually specify a date(and/or time) as a suffix to prevent that
probleb and keep several versions of backup.

Not that it in this case, but it is considered good procedure to report the
PostgreSQL version and O/S when contacting this list for support.
Please consider that for future correspondence.


-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

[GENERAL] Aborted pg_dump run empties existing archive file

[Ken Winter]

I just discovered that a run of pg_dump that gets aborted empties any
pre-existing backup file of the same name.  It happens whether the run was
deliberately canceled by the user or just failed because of a bad password
(as in the example below).

~/dba$ pg_dump --host=localhost -U ken  --format=c
--file=mess_development.bak --no-owner --no-privileges mess_development
Password: 

~/dba$ ls -l mess_development.bak
-rw-rw-r-- 1 ken ken *87070* May 26 14:20 mess_development.bak

~/dba$ pg_dump --host=localhost -U ken  --format=c
--file=mess_development.bak --no-owner --no-privileges mess_development
Password: 
pg_dump: [archiver (db)] connection to database "mess_development" failed:
FATAL:  password authentication failed for user "ken"
FATAL:  password authentication failed for user "ken"

~/dba$ ls -l mess_development.bak
-rw-rw-r-- 1 ken ken *0* May 26 14:21 mess_development.bak

~/dba$

This is troubling because I can imagine a disaster scenario where one loses
one's previous backup and, perhaps because of a forgotten password, can't
generate a new one.  (This scenario hasn't happened to *me* yet, but seems
like it could.)  In general, a failed run of a piece of software should
leave everything unchanged.  So maybe this is a bug that needs to be fixed?

~ Ken

Re: [GENERAL] full text search index

[Kevin Grittner]

You don't provide much context, like PostgreSQL version or machine
characteristics.

https://wiki.postgresql.org/wiki/SlowQueryQuestions

On Wed, May 25, 2016 at 11:04 PM, Patrick Baker
 wrote:

>> SELECT COUNT(DISTINCT j0_.id) AS sclr10
>> FROM customers j0_
>> WHERE ((LOWER(j0_.name_first) LIKE '%some%'
>> OR LOWER(j0_.name_last) LIKE '%some%')
>>AND j0_.id = 5)
>>   AND j0_.id = 5
>
> The query is taking ages to run.
>
> I read about wildcards and it seems I have to use a function with
> to_tsvector ?

I very much doubt that full text search is going to be helpful here
-- perhaps trigrams with an appropriate gist or gin index could
help.  Depending on table sizes and data present, picking out rows
based on the OR of scanning for a sequence of characters in a
couple character string columns might not be your fastest query to
run.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
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] Subquery uses ungrouped column

[Tom Lane]

"David G. Johnston"  writes:
> From observation PostgreSQL doesn't recognize the equivalency of the
> outer "group by substring(fileid from -1)" and the subquery expression.

Well, it would without the subselect in between.  There's a code comment
in parse_agg.c about this:

 * NOTE: we recognize grouping expressions in the main query, but only
 * grouping Vars in subqueries.  For example, this will be rejected,
 * although it could be allowed:
 *  SELECT
 *  (SELECT x FROM bar where y = (foo.a + foo.b))
 *  FROM foo
 *  GROUP BY a + b;
 * The difficulty is the need to account for different sublevels_up.
 * This appears to require a whole custom version of equal(), which is
 * way more pain than the feature seems worth.

It'd probably be possible to fix parse_agg.c if you didn't mind expending
lots of cycles on such cases.  I'm not sure offhand whether there would
be implications in the planner, or what it would take to fix them if so.

regards, tom lane


-- 
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] Subquery uses ungrouped column

[David G. Johnston]

On Thu, May 26, 2016 at 12:02 PM, Alex Ignatov 
wrote:

> Hello!
>
> Why the following query:
>
> SELECT (select msc_id
>   from collectors
>   where id = substring(fileid from -1)
>) msc_id
>from ip_data_records
>group by substring(fileid from -1)
>
> gives me:
>
> ERROR:  subquery uses ungrouped column "ip_data_records.fileid" from outer
> query
> LINE 3: where id = substring(fileid from -1)
>
> but  the following query:
>
> SELECT (select msc_id
>   from collectors
>   where id = fileid
>) msc_id
>from ip_data_records
>group by fileid
> is working ok
>

>From observation ​PostgreSQL​

​doesn't recognize the equivalency of the outer "group by substring(fileid
from -1)" and the subquery expression.  What PostgreSQL does is push the
column ip_data_records.fieldid​ into the subquery where it just happens to
be used in the expression "substring(fileid from -1)".  For all PostgreSQL
cares the subquery could have the expression "where id = lower(fileid)" and
the execution mechanics, and error, would be identical.

IOW, columns are the unit of interchange between a parent query and its
correlated subqueries.​

David J.

[GENERAL] Subquery uses ungrouped column

[Alex Ignatov]

Hello!

Why the following query:

SELECT (select msc_id
  from collectors
  where id = substring(fileid from -1)
   ) msc_id
   from ip_data_records
   group by substring(fileid from -1)

gives me:

ERROR:  subquery uses ungrouped column "ip_data_records.fileid" from 
outer query

LINE 3: where id = substring(fileid from -1)

but  the following query:

SELECT (select msc_id
  from collectors
  where id = fileid
   ) msc_id
   from ip_data_records
   group by fileid

is working ok?

--
Alex Ignatov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

Re: [GENERAL] Do docs miss information about timing of triggers?

[Thomas Güttler]

Yes, you are right.

But "after" the statement could mean before commit, too.

Why not add this?

Proposal:

When no CONSTRAINT option is specified, this command creates a normal trigger. 
They
get fired at the end of the statement (IMMEDIATE).

Regards,
  Thomas Güttler

Am 26.05.2016 um 15:43 schrieb Tom Lane:

"David G. Johnston"  writes:

On Thu, May 26, 2016 at 8:50 AM, Thomas GÃŒttler <
guettl...@thomas-guettler.de> wrote:

OK, timing of constraint triggers is explained.
But I think the docs don't state the timing of normal AFTER triggers.



​Through omission.


It's not *that* bad.  See
https://www.postgresql.org/docs/9.5/static/trigger-definition.html

 Triggers are also classified according to whether they fire before,
 after, or instead of the operation. These are referred to as BEFORE
 triggers, AFTER triggers, and INSTEAD OF triggers
 respectively. Statement-level BEFORE triggers naturally fire before
 the statement starts to do anything, while statement-level AFTER
 triggers fire at the very end of the statement. These types of
 triggers may be defined on tables or views. Row-level BEFORE triggers
 fire immediately before a particular row is operated on, while
 row-level AFTER triggers fire at the end of the statement (but before
 any statement-level AFTER triggers). ...

regards, tom lane



--
Thomas Guettler http://www.thomas-guettler.de/


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

[GENERAL] Permission Denied Error on pg_xlog/RECOVERYXLOG file

[Jeffrey.Marshall]

Hi Folks,

We reported a bug last month (#14119) related to receiving a permission denied 
error on the pg_xlog/RECOVERYXLOG file when performing a database recovery 
(PostgreSQL versions 9.2.16 and 9.2.17).  The bug report was for version 9.2.16 
but we also are having the same problem with 9.2.17.  We were just curious if 
anyone else running 9.2.16 or 9.2.17 had encountered this issue.

The link to the bug report is below, but much of the information is included 
here as well.


When performing a vanilla database restore using either the 9.2.16 or 9.2.17 
executables (i.e. just restoring the database files from a 'tar' backup and 
reading the WAL files created during the 'tar' backup - no specific PIT given 
in recovery.conf) the database server will abort with a permission denied error 
on the pg_xlog/RECOVERYXLOG file.  The error occurred restoring both backups 
that were made under the current version (9.2.16 and 9.2.17) as well as backups 
made under prior versions (9.2.15 at least).  The exact same restore 
process/backup files can be used to successfully restore the database using the 
9.2.15 executables, but fail when using either 9.2.16 or 9.2.17 with the 
permission denied error.


ENVIRONMENT:   PostgreSQL 9.2.16

[postgres-pt(at)postXX pg_xlog]$ psql --version
psql (PostgreSQL) 9.2.16

[postgres-pt(at)postXX pg_xlog]$ psql
psql (9.2.16)
Type "help" for help.

admin_db=# select version();
version

PostgreSQL 9.2.16 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7
20120313 (Red Hat 4.4.7-16), 64-bit
(1 row)


OPERATING SYSTEM INFORMATION:

The database server is running on a Redhat Linux host (Red Hat Enterprise Linux 
Server release 6.8 (Santiago)) -

[postgres-pt(at)postXX pg_xlog]$ uname -a
Linux  2.6.32-573.22.1.el6.x86_64 #1 SMP Thu Mar 17 03:23:39 EDT 2016 x86_64 
x86_64 x86_64 GNU/Linux


The Postgres executables were downloaded/installed using rpm/yum (yum update 
postgresql92-server) and not compiled locally (same download/install process as 
prior versions) - no additional modules/no modifications have been made.



STEPS TAKEN TO PRODUCE ERROR:

The restores being performed are not to a specific point in time (i.e. restore 
the database as of 4/18/16 09:59:17); the restores are just loading the 
database as of the completion of the last full backup.


Perform a database backup:

select pg_start_backup('backup_2016_04_19');

tar and compress the tablespace directories (2 tablespace directories in this 
case) and the cluster directory
save any WAL files created during the backup

select pg_stop_backup();



Restore the database:

Remove tablespace directories
Remove cluster directory
Uncompress/extract the tablespace directories
Uncompress/extract the cluster directory
Remove files from pg_xlog directory
Create a recovery.conf file that points to the directory with the WAL archive 
files created during the backup

restart the server using the 9.2.16 executables

pg_ctl -D /pgdata/pgsrv_cluster_data/postXX_rest_92  start



Message from database error log:

2016-04-27 17:02:05 EDT 572128cd.1811 [1-1] user=,db=,remote= LOG:  0:
database system was interrupted; last known up at 2016-04-19 14:18:03 EDT
2016-04-27 17:02:05 EDT 572128cd.1811 [2-1] user=,db=,remote= LOCATION:
StartupXLOG, xlog.c:6372
2016-04-27 17:02:05 EDT 572128cd.1811 [3-1] user=,db=,remote= LOG:  0:
starting archive recovery
2016-04-27 17:02:05 EDT 572128cd.1811 [4-1] user=,db=,remote= LOCATION:
StartupXLOG, xlog.c:6465
2016-04-27 17:02:06 EDT 572128cd.1811 [5-1] user=,db=,remote= LOG:  0:
restored log file "0001000600FE" from archive
2016-04-27 17:02:06 EDT 572128cd.1811 [6-1] user=,db=,remote= LOCATION:
RestoreArchivedFile, xlog.c:3267
2016-04-27 17:02:06 EDT 572128cd.1811 [7-1] user=,db=,remote= FATAL:  42501:
could not open file "pg_xlog/RECOVERYXLOG": Permission denied
2016-04-27 17:02:06 EDT 572128cd.1811 [8-1] user=,db=,remote= LOCATION:
fsync_fname_ext, fd.c:2654
2016-04-27 17:02:06 EDT 572128cc.180f [3-1] user=,db=,remote= LOG:  0:
startup process (PID 6161) exited with exit code 1
2016-04-27 17:02:06 EDT 572128cc.180f [4-1] user=,db=,remote= LOCATION:
LogChildExit, postmaster.c:3008
2016-04-27 17:02:06 EDT 572128cc.180f [5-1] user=,db=,remote= LOG:  0:
aborting startup due to startup process failure
2016-04-27 17:02:06 EDT 572128cc.180f [6-1] user=,db=,remote= LOCATION:
reaper, postmaster.c:2461


Thanks,
Jeff Marshall


Link to bug report:  
http://www.postgresql.org/message-id/20160428132828.19110.9...@wrigleys.postgresql.org

Re: [GENERAL] full text search index

[Alban Hertroys]

On 26 May 2016 at 06:04, Patrick Baker  wrote:
> Hi there,
>
> I've got the following query:
>>
>>
>> SELECT COUNT(DISTINCT j0_.id) AS sclr10
>> FROM customers j0_
>> WHERE ((LOWER(j0_.name_first) LIKE '%some%'
>> OR LOWER(j0_.name_last) LIKE '%some%')
>>AND j0_.id = 5)
>>   AND j0_.id = 5
>
>
> The query is taking ages to run.

Your guess is as good as ours without knowing what query plan the
database decided on. Post the output of explain analyze.

> I read about wildcards and it seems I have to use a function with
> to_tsvector ?

In general, you have to use an expression of which the query planner
can see that it's equivalent to the expression used in the index.
Otherwise the query planner has no way of knowing whether the index is
suitable for the query and it won't use the index.

>> CREATE INDEX CONCURRENTLY ON public.customers USING gin ("clientid",
>> ("full_text_universal_cast"("name_first"::"text")),
>> ("full_text_universal_cast"("name_last"::"text")));

In your case, you should query on full_text_universal_cast(your_field)
instead of on like '%some%'.

Alternatively, if your query always uses the sanme wildcard expression
you could create indexes on your_field like '%some%'.

> full_text_universal_cast:
>>
>> CREATE OR REPLACE FUNCTION public.full_text_universal_cast(doc_data
>> "text")
>>   RETURNS "tsvector" AS
>> $BODY$
>> SELECT to_tsvector('english', COALESCE(TRIM(CAST(doc_data AS TEXT)), ''));
>> $BODY$
>>   LANGUAGE sql IMMUTABLE
>>   COST 1000;

The query planner has no way of knowing what this function does
internally, so it certainly won't match the function results in the
index up with your like expression.

Regards,

Alban Hertroys
-- 
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.


-- 
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] Do docs miss information about timing of triggers?

[Adrian Klaver]

On 05/26/2016 05:50 AM, Thomas Güttler wrote:

I (and a team mate) guess that the docs miss information about the
timing of triggers,
which are not constraint triggers:

https://www.postgresql.org/docs/devel/static/sql-createtrigger.html


{{{
When the CONSTRAINT option is specified, this command creates a
constraint trigger. This is the same as a regular trigger except that
the timing of the trigger firing can be adjusted using SET CONSTRAINTS.
Constraint triggers must be AFTER ROW triggers on tables. They can be
fired either at the end of the statement causing the triggering event,
or at the end of the containing transaction; in the latter case they are
said to be deferred. A pending deferred-trigger firing can also be
forced to happen immediately by using SET CONSTRAINTS. Constraint
triggers are expected to raise an exception when the constraints they
implement are violated.
}}}

OK, timing of constraint triggers is explained.

But I think the docs don't state the timing of normal AFTER triggers.

Or am I blind?


Look about seven paragraphs up from the one you show above.



Regards,
  Thomas Güttler





--
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] Do docs miss information about timing of triggers?

[Tom Lane]

"David G. Johnston"  writes:
> On Thu, May 26, 2016 at 8:50 AM, Thomas Güttler <
> guettl...@thomas-guettler.de> wrote:
>> OK, timing of constraint triggers is explained.
>> But I think the docs don't state the timing of normal AFTER triggers.

> ​Through omission.

It's not *that* bad.  See
https://www.postgresql.org/docs/9.5/static/trigger-definition.html

Triggers are also classified according to whether they fire before,
after, or instead of the operation. These are referred to as BEFORE
triggers, AFTER triggers, and INSTEAD OF triggers
respectively. Statement-level BEFORE triggers naturally fire before
the statement starts to do anything, while statement-level AFTER
triggers fire at the very end of the statement. These types of
triggers may be defined on tables or views. Row-level BEFORE triggers
fire immediately before a particular row is operated on, while
row-level AFTER triggers fire at the end of the statement (but before
any statement-level AFTER triggers). ...

regards, tom lane


-- 
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] Do docs miss information about timing of triggers?

[David G. Johnston]

On Thu, May 26, 2016 at 8:50 AM, Thomas Güttler <
guettl...@thomas-guettler.de> wrote:

> I (and a team mate) guess that the docs miss information about the timing
> of triggers,
> which are not constraint triggers:
>
> https://www.postgresql.org/docs/devel/static/sql-createtrigger.html
>
>
> {{{
> When the CONSTRAINT option is specified, this command creates a constraint
> trigger. This is the same as a regular trigger except that the timing of
> the trigger firing can be adjusted using SET CONSTRAINTS. Constraint
> triggers must be AFTER ROW triggers on tables. They can be fired either at
> the end of the statement causing the triggering event, or at the end of the
> containing transaction; in the latter case they are said to be deferred. A
> pending deferred-trigger firing can also be forced to happen immediately by
> using SET CONSTRAINTS. Constraint triggers are expected to raise an
> exception when the constraints they implement are violated.
> }}}
>
> OK, timing of constraint triggers is explained.
>
> But I think the docs don't state the timing of normal AFTER triggers.
>
> Or am I blind?
>

​Through omission.  Constraint triggers can optionally be deferred - given
the specificity that means normal triggers cannot.

By the time a given statement has completed all relevant normal triggers
will have fired.  The various timings of combinations of (before/after +
row/statement) are not explicitly documented though there doesn't seem to
be non-intuitive behavior going on.

​Maybe knowing why you are asking the question will help us to understand
if/how things could be improved.

David J.
​

​

[GENERAL] Do docs miss information about timing of triggers?

[Thomas Güttler]

I (and a team mate) guess that the docs miss information about the timing of 
triggers,
which are not constraint triggers:

https://www.postgresql.org/docs/devel/static/sql-createtrigger.html


{{{
When the CONSTRAINT option is specified, this command creates a constraint trigger. This is the same as a regular 
trigger except that the timing of the trigger firing can be adjusted using SET CONSTRAINTS. Constraint triggers must be 
AFTER ROW triggers on tables. They can be fired either at the end of the statement causing the triggering event, or at 
the end of the containing transaction; in the latter case they are said to be deferred. A pending deferred-trigger 
firing can also be forced to happen immediately by using SET CONSTRAINTS. Constraint triggers are expected to raise an 
exception when the constraints they implement are violated.

}}}

OK, timing of constraint triggers is explained.

But I think the docs don't state the timing of normal AFTER triggers.

Or am I blind?

Regards,
  Thomas Güttler


--
Thomas Guettler http://www.thomas-guettler.de/


--
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] full text search index

[Patrick Baker]

>
>
>
> Maybe Lucas Possamai can help.  He seems to be a little bit further along
> in this exercise.
>
> I'm too tired to care at the moment.  And I haven't had much practical
> work here anyway.
>
> David J.
>
>
>
>

I subscribed to the list today, so don't have the old emails

I had a look on the archives tough, Lucas do you have any progress? If so,
please let me know :)


Thanks
Patrick

Re: [GENERAL] empty pg_stat_replication when replication works fine?

[Andrej Vanek]

The instance is still running, I tried to collect more information from it:

all databases are working as expected,
the only issue is that monitoring SQL commands (pg_stat_activity,
pg_stat_replication) are not working as expected (do not reflect postgres
processes list from command-line)

on Master:
- pg_stat_activity is empty as well (they can  be seen just in ps f -fu
postgres output: CTSYSTEM lines)
- psql as postgres: select * from pg_stat_activity sees only its own session
- psql as unprivileged user (CTSYSTEM): select * from pg_stat_activity is
empty
- replication works fine (created a table, that was created also on all
replicas)
- added lines to postgresql.conf + reload:

Opening new  lines to postgresql.conf + reload configuration:
client_min_messages = debug5
log_min_messages = debug5
log_min_error_statement = debug5
- activity seen in pg_log, also replication activity (pgreplic user) is
seen, still nothing in pg_stat_replication/pg_stat_activity

killed one slave postgres instance, restarted it
- "standby "l2abrnch" has now caught up with primary"
- replication works fine
- no entries on Master in pg_stat_replication
- ps -ef shows the new wal-sender process on master and wal-receiver
process streaming on this slave

Version is:
PostgreSQL 9.3.10 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7
20120313 (Red Hat 4.4.7-16), 64-bit

I suspect something happened within master server
(pg_stat_activity+pg_stat_replication not working as described, do not
reflect ps -ef list of postgres processes and running SQL
client/replication  information)

What may be additionally useful information before restarting the master?

Regards, Andrej

2016-05-25 23:22 GMT+02:00 Andrej Vanek :

> Streaming replication set-up,
>
> one master, 3 slaves connecting to it.
> I expected ps -ef gets all wal-sender processes and SAME information I'll
> get via select * from pg_stat_replication.
> Instead I observed:
> - pg_stat_replication is empty
> - 3 wal-sender processes up and running
> - each slave has wal-receiver process running
> - replication works (tried to create a table- it appears in all databases)
> Question:
> - why is pg_stat_replication empty?
>
> Andrej
> ---details
> [root@l2bmain ~]# tail /opt/pg_data/postgresql.conf
> max_wal_senders = 5
> hot_standby = on
> wal_keep_segments = 128
> archive_command = '/opt/postgres/dbconf/archive_command.sh %p %f'
> wal_receiver_status_interval = 2
> max_standby_streaming_delay = -1
> max_standby_archive_delay = -1
> restart_after_crash = off
> hot_standby_feedback = on
> wal_sender_timeout = 1min
> [root@l2bmain ~]# ps f -fu postgres
> UIDPID  PPID  C STIME TTY  STAT   TIME CMD
> postgres 10797 1  0 15:53 ?S  0:20
> /usr/pgsql-9.3/bin/postgres -D /opt/pg_data -c
> config_file=/opt/pg_data//postgresql.conf
> postgres 10820 10797  0 15:53 ?Ss 0:00  \_ postgres: logger
> process
> postgres 10823 10797  0 15:53 ?Ss 0:00  \_ postgres:
> checkpointer process
> postgres 10824 10797  0 15:53 ?Ss 0:00  \_ postgres: writer
> process
> postgres 10825 10797  0 15:53 ?Ss 0:00  \_ postgres: wal
> writer process
> postgres 10826 10797  0 15:53 ?Ss 0:01  \_ postgres:
> autovacuum launcher process
> postgres 10827 10797  0 15:53 ?Ss 0:00  \_ postgres: archiver
> process   last was 000100A3.0028.backup
> postgres 10828 10797  0 15:53 ?Ss 0:03  \_ postgres: stats
> collector process
> postgres 11286 10797  0 15:54 ?Ss 0:08  \_ postgres: wal
> sender process pgreplic 192.168.204.12(55231) streaming 0/A401BED8
> postgres 11287 10797  0 15:54 ?Ss 0:06  \_ postgres: wal
> sender process pgreplic 192.168.204.11(42937) streaming 0/A401BED8
> postgres 19322 10797  0 15:58 ?Ss 0:08  \_ postgres: wal
> sender process pgreplic 192.168.101.11(52379) streaming 0/A401BED8
> postgres 28704 10797  0 18:44 ?Ss 0:00  \_ postgres: CTSYSTEM
> lidb 192.168.102.13(58245) idle
> postgres  7256 10797  0 18:52 ?Ss 0:00  \_ postgres: CTSYSTEM
> lidb 192.168.102.23(55190) idle
> postgres  8667 10797  0 18:53 ?Ss 0:00  \_ postgres: CTSYSTEM
> lidb 192.168.102.13(58287) idle
> [root@l2bmain ~]# psql -U postgres -c "select * from pg_stat_replication;"
>  pid | usesysid | usename | application_name | client_addr |
> client_hostname | client_port | backend_start | state | sent_location |
> write_location | flush_location | r
> eplay_location | sync_priority | sync_state
>
> -+--+-+--+-+-+-+---+---+---+++--
> ---+---+
> (0 rows)
>
> [root@l2bmain ~]# tail /opt/pg_data/pg_log/postgresql-Wed.log
> 2016-05-25 15:53:56 CEST:@:[8603] LOG:  database system is shut down
> 2016-05-25 15:53:58 CEST:@:[10821]