Re: PG 9.2 slave restarted - cache not impacted

[Michael Paquier]

On Fri, Oct 23, 2020 at 11:23:20AM +1300, Lucas Possamai wrote:
> I'm a bit confused about PG cache.
> 
> I have a PostgreSQL 9.2 cluster (yes, we're planning on upgrading it to 12)
> with a master and a slave database.
> 
> The application is sending all read requests to the slave, where the master
> processes the writes.
> 
> A while ago we had to restart the master database server, and the
> application was slow for a couple of days while PG cache was warmed up.
> Yesterday, we had to restart the slave database server and we did not have
> the same problem.
> 
> I would like to understand why?

Perhaps you misunderstood the effects of the OS cache and the Postgres
shared buffers?  On restart the cluster discards the shared buffers
internal to Postgres.  It does not mean that the OS cache is changed,
and it matters a lot in terms of performance.  By the way, if you want
to warm up your caches faster, an option you can consider with 9.2
(which is a version not officially supported by the community by the
way so please upgrade) would be to use pgfincore.
--
Michael


signature.asc
Description: PGP signature

PG 9.2 slave restarted - cache not impacted

[Lucas Possamai]

Hi guys,

I'm a bit confused about PG cache.

I have a PostgreSQL 9.2 cluster (yes, we're planning on upgrading it to 12)
with a master and a slave database.

The application is sending all read requests to the slave, where the master
processes the writes.

A while ago we had to restart the master database server, and the
application was slow for a couple of days while PG cache was warmed up.
Yesterday, we had to restart the slave database server and we did not have
the same problem.

I would like to understand why?

Thanks!

Re: Hot backup in PostgreSQL

[Mark Johnson]

User managed backups in PostgreSQL work very similar to what you know from
Oracle.  You first place the cluster in backup mode, then copy the database
files, and lastly take the cluster out of backup mode.  The first and last
steps are done using functions pg_start_backup('label',false,false) and
pg_stop_backup(false, false). [1].

If you use a utility supplied with PostgreSQL such as pg_basebackup, it
does these steps for you.  If you are using a specific non-PostgreSQL
utility (i.e., Dell Networker or IBM Tivoli) see their documentation for
specifics.

[1]
https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-BACKUP.


On Thu, Oct 22, 2020 at 11:14 AM Adam Brusselback 
wrote:

> > how to do "hot backup" (copying files) while database running?
> As others have shown, there are ways to do this with PG's internal tooling
> (pg_basebackup).
>
> However, I would highly recommend you use an external backup tool like
> pgbackrest [1] to save yourself the pain of implementing things incorrectly
> and ending up with non-viable backups when you need them most. I'm not
> affiliated with them at all, but have just used pgbackrest in production
> for years now with great results.  It takes care of PITR, and manages
> backup retention (and associated WAL retention). Those can be a bit of a
> pain to do manually otherwise.
>
> Just my $0.02, hope it helps!
>
> 1. https://pgbackrest.org/
>

postgres materialized view refresh performance

[Ayub M]

There is a table t which is used in a mview mv, this is the only table in
the mview definition.

create table t (c1 int, ..., c10 int);-- there is a pk on say c1
columncreate materialized view mv as select c1, c2...c10 from
t;---there is a unique index on say c5 and bunch of other indexes on
the mview.

The reason there is a mview created instead of using table t, is that that
the table gets truncated and reloaded every couple of hours and we don't
want users to see an empty table at any point of time that's why mview is
being used.

Using "refresh materialized view concurrently", this mview is being used by
APIs and end users.

Couple of questions I have -

   1. Whenever mview refresh concurrently happens, does pg create another
   set of table and indexes and switch it with the orig? If no, then does it
   update the existing data?
   2. If the usage of mview is pretty heavy does it impact the performance
   of the refresh process? Vice-versa, if the refresh is going on does the
   performance of mview by users take a hit?
   3. The mview gets refreshed in a couple of mins sometimes and sometimes
   it takes hours. When it runs for longer, there are no locks and no resource
   shortage, the number of recs in the base table is 6m (7.5gb) which is not
   huge so why does it take so long to refresh the mview?
   4. Does mview need vacuum/analyze/reindex?

Re: Hot backup in PostgreSQL

[Adam Brusselback]

> how to do "hot backup" (copying files) while database running?
As others have shown, there are ways to do this with PG's internal tooling
(pg_basebackup).

However, I would highly recommend you use an external backup tool like
pgbackrest [1] to save yourself the pain of implementing things incorrectly
and ending up with non-viable backups when you need them most. I'm not
affiliated with them at all, but have just used pgbackrest in production
for years now with great results.  It takes care of PITR, and manages
backup retention (and associated WAL retention). Those can be a bit of a
pain to do manually otherwise.

Just my $0.02, hope it helps!

1. https://pgbackrest.org/

Re: Initplan placed at the righttree or the lefttree of joinnode

[Tom Lane]

"Hou, Zhijie"  writes:
> Can Initplan placed at the righttree or the lefttree of joinnode?

Initplans generally get attached to the topmost node of a given
(sub)query, cf create_plan().  You might be able to get what you want
by having an unflattenable subquery as one input of a join.  I do not
think you're going to get any exciting results that way though ...
as create_plan() notes, the exact spot where an initplan is attached
to the tree isn't terribly relevant.

regards, tom lane

Re: Hot backup in PostgreSQL

[Sushant Pawar]

Hi,

If the requirement is to take online backup(hot) backup that can be used to
do point in time recovery, you can rely on low-level API functionality
mentioned earlier by Paul. If you to be care free about not missing any
specific mount point other than default mount point, you can use
pg_basebackup for online backup.

Pg_basebackup will take care of backing up all database files including
custom tablespace and has the option to compress the backup.You can refer
to below link for more information.

https://www.postgresql.org/docs/11/app-pgbasebackup.html



On Thu, Oct 22, 2020 at 12:12 PM W.P.  wrote:

> Hi there,
>
> how to do "hot backup" (copying files) while database running?
>
> Not using pg_dump.
>
> Is there some equivalent of Oracle (I knew v8) "ALTER DATABASE BEGIN
> BACKUP", which froze writes to database files, pushing everything to
> redo files?
>
>
> Laurent.
>
>
>
>

Re: How to get debuginfo from building source code

[Devrim Gündüz]

Hi,

On Thu, 2020-10-22 at 02:49 +, Hou, Zhijie wrote:
> 
> My PostgreSQL 10.3 in Centos7.4 exited abnormally and generated
> core.dump.
> 
> I want to analyze the core.dump but I does not find postgresql10-
> debuginfo-10.3 in
> 
> https://download.postgresql.org/pub/repos/yum/debug/10/redhat/rhel-7.4-x86_64/

10.3 is 2,5 years old. I would update to 10.14 first.

Regards,

-- 
Devrim Gündüz
Open Source Solution Architect, Red Hat Certified Engineer
Twitter: @DevrimGunduz , @DevrimGunduzTR


signature.asc
Description: This is a digitally signed message part

Re: Initplan placed at the righttree or the lefttree of joinnode

[Laurenz Albe]

On Thu, 2020-10-22 at 09:43 +, Hou, Zhijie wrote:
> Hi,
> 
> I try to make some special query plan like the following.
> I have tried a lot SQL, but failed. 
> Can Initplan placed at the righttree or the lefttree of joinnode?
> 
>   (Fake) QUERY PLAN
> ---
>  Hash Join  (cost=13.15..26.48 rows=140 width=8)
>Hash Cond: (test1.a = test.a)
>->  Seq Scan on test1  (cost=0.00..11.40 rows=140 width=4)
>->  Hash  (cost=11.40..11.40 rows=140 width=8) 
> ->  InitPlan 1 (returns $0,$1)
> Or
> 
>  (Fake)  QUERY PLAN
> ---
>  Hash Join  (cost=13.15..26.48 rows=140 width=8)
>Hash Cond: (test1.a = test.a)
>->  Seq Scan on test1  (cost=0.00..11.40 rows=140 width=4)
> ->  InitPlan 1 (returns $0,$1)
>->  Hash  (cost=11.40..11.40 rows=140 width=8)

Do you mean something like this (with the pgbench table):

EXPLAIN SELECT * FROM pgbench_accounts a JOIN pgbench_accounts b USING (aid) 
WHERE a.bid = (SELECT 42);
   QUERY PLAN   


 Hash Join  (cost=31707.01..93539.02 rows=10 width=190)
   Hash Cond: (b.aid = a.aid)
   InitPlan 1 (returns $0)
 ->  Result  (cost=0.00..0.01 rows=1 width=4)
   ->  Seq Scan on pgbench_accounts b  (cost=0.00..26394.00 rows=100 
width=97)
   ->  Hash  (cost=28894.00..28894.00 rows=10 width=97)
 ->  Seq Scan on pgbench_accounts a  (cost=0.00..28894.00 rows=10 
width=97)
   Filter: (bid = $0)
(8 rows)

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Initplan placed at the righttree or the lefttree of joinnode

[Hou, Zhijie]

Hi,

I try to make some special query plan like the following.
I have tried a lot SQL, but failed. 
Can Initplan placed at the righttree or the lefttree of joinnode?

  (Fake) QUERY PLAN
---
 Hash Join  (cost=13.15..26.48 rows=140 width=8)
   Hash Cond: (test1.a = test.a)
   ->  Seq Scan on test1  (cost=0.00..11.40 rows=140 width=4)
   ->  Hash  (cost=11.40..11.40 rows=140 width=8) 
->  InitPlan 1 (returns $0,$1)
Or

 (Fake)  QUERY PLAN
---
 Hash Join  (cost=13.15..26.48 rows=140 width=8)
   Hash Cond: (test1.a = test.a)
   ->  Seq Scan on test1  (cost=0.00..11.40 rows=140 width=4)
->  InitPlan 1 (returns $0,$1)
   ->  Hash  (cost=11.40..11.40 rows=140 width=8)

Best regards

Re: Hot backup in PostgreSQL

[Andreas Joseph Krogh]

På torsdag 22. oktober 2020 kl. 10:18:12, skrev hubert depesz lubaczewski <
dep...@depesz.com >: 
On Thu, Oct 22, 2020 at 09:45:36AM +0200, W.P. wrote:
 > > There are many ways to do it. To be able to suggest proper solution we'd
 > > need to know:
 > > 1. what is the problem with pg_dump?
 > Time (I guess a bit, but copying files could be done using rsync, so much
 > faster).

 Is it *really* too slow for you? Please note that you can easily make it
 much faster by doing -Fd -j $( nproc ). 

I got curious and tried with this DB: 

andreak@[local]:5433 13.0 visena=# select 
pg_size_pretty(pg_database_size(current_database()));
 ┌┐
 │ pg_size_pretty │
 ├┤
 │ 47 GB │
 └┘
 (1 row)


nproc=16


Regular pg_dump: 

$ time pg_dump -O -d visena > ~/data/visena/visena.dmp

 real 2m43,904s
 user 0m10,135s
 sys 0m24,260s 


Parallell pg_dump: 

$ time pg_dump -OFd -j $(nproc) -f ~/data/visena/pg_backup -d visena 

 real 3m43,726s
 user 12m36,620s
 sys 0m9,537s


pg_dump with pbzip2 

$ time pg_dump -O -d visena | pbzip2 -c > ~/data/visena/visena.dmp.bz2 

 real 6m58,741s
 user 92m4,833s
 sys 2m18,565s 

Here are the sizes of all: 

7,4G pg_backup (directory with -Fd)
32G visena.dmp 
 5,8G visena.dmp.bz2 

-- 

Andreas Joseph Krogh 
CTO / Partner - Visena AS 
Mobile: +47 909 56 963 
andr...@visena.com  
www.visena.com  
  

Re: Setup Pgpool2 with Postgresql Streaming Replication

[Tatsuo Ishii]

Hi Alan,

This is not the best forum to discuss Pgpool-II related topics. I
advice you to go to the Pgpool-II dedicated forum:

https://www.pgpool.net/mailman/listinfo/pgpool-general

> Hi,
> 
> I'm following the steps from:
> 
> https://access.crunchydata.com/documentation/pgpool/4.0.0/example-cluster.html

This documentation looks pretty old. The original and the latest
documentation for Pgpool-II 4.0 is here (I assume you are using
Pgpool-II 4.0):

https://www.pgpool.net/docs/40/en/html/example-cluster.html

I strongly suggest to look into this.

> I'm at step 7.3.8.1 Set up PostgreSQL standby server:
> Ran this command on the primary server: pcp_recovery_node -h 192.168.80.90
> -p 9898 -U postgres -n 1
> And received this error: 
> ERROR:  recovery is checking if postmaster is started
> DETAIL:  postmaster on hostname:"ltpgsql12" database:"template1"
> user:"postgres" failed to start in 90 second
> 
> How can I get this command to run successfully?

Probably you have a problem with ssh settings. The newer and original
document describes far detailed steps to set up ssh settings. Please
take a look at "7.3.2. Requirements" section in the newer document.

In the mean time to confirm that the problem is related to ssh, we
need to look into the PostgreSQL log (not Pgpool-II log) on primary
PostgreSQL node. Please share it (again, you'd better to post messages
to the pgpool-general mailing list).

> Also, when I ran this command: psql -p 5433 -c "show pool_nodes"
> It shows the following pgpool2 node status, but when I did a listing of
> databases on the primary and standby servers, I don't see the databases on
> the primary replicated to the standby.  How can I setup the Postgresql
> Streaming Replication and check if it's working?

Standby status is down because you failed to execute online
recovery. You need to fix it.

> node_id | hostname  | port | status | lb_weight |  role   | select_cnt |
> load_balance_node | replication_delay | replication_state |
> replication_sync_state | last_status_change
> -+---+--++---+-++---+---+---++-
>  0   | ltpgsql11 | 6432 | up | 0.50  | primary | 0  |
> true  | 0 |   |   
> 
> | 2020-10-21 11:56:48
>  1   | ltpgsql12 | 6432 | down   | 0.50  | standby | 0  |
> false | 0 |   |   
> 
> | 2020-10-21 11:56:48
> (2 rows)
> 
> 
> Thanks,
> Alan
> 
> 
> 
> --
> Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
> 
> 

Re: Hot backup in PostgreSQL

[hubert depesz lubaczewski]

On Thu, Oct 22, 2020 at 09:45:36AM +0200, W.P. wrote:
> > There are many ways to do it. To be able to suggest proper solution we'd
> > need to know:
> > 1. what is the problem with pg_dump?
> Time (I guess a bit, but copying files could be done using rsync, so much
> faster).

Is it *really* too slow for you? Please note that you can easily make it
much faster by doing -Fd -j $( nproc ).

> > 2. what is the exact problem you're solving (clearly it's not only
> > "having backup", as this is done using pg_dump without any problem).
> Maybe this is old way, but at some point of time I was doing Oracle 8
> backups just by copying files.
> Also I guess, restore using copy files should be much faster than using psql
> / pg_restore.

You might want to read this:
https://www.postgresql.org/docs/current/continuous-archiving.html#BACKUP-LOWLEVEL-BASE-BACKUP

Best regards,

depesz

Setup Pgpool2 with Postgresql Streaming Replication

[alanhi]

Hi,

I'm following the steps from:

https://access.crunchydata.com/documentation/pgpool/4.0.0/example-cluster.html

I'm at step 7.3.8.1 Set up PostgreSQL standby server:
Ran this command on the primary server: pcp_recovery_node -h 192.168.80.90
-p 9898 -U postgres -n 1
And received this error: 
ERROR:  recovery is checking if postmaster is started
DETAIL:  postmaster on hostname:"ltpgsql12" database:"template1"
user:"postgres" failed to start in 90 second

How can I get this command to run successfully?

Also, when I ran this command: psql -p 5433 -c "show pool_nodes"
It shows the following pgpool2 node status, but when I did a listing of
databases on the primary and standby servers, I don't see the databases on
the primary replicated to the standby.  How can I setup the Postgresql
Streaming Replication and check if it's working?

node_id | hostname  | port | status | lb_weight |  role   | select_cnt |
load_balance_node | replication_delay | replication_state |
replication_sync_state | last_status_change
-+---+--++---+-++---+---+---++-
 0   | ltpgsql11 | 6432 | up | 0.50  | primary | 0  |
true  | 0 |   | 
  
| 2020-10-21 11:56:48
 1   | ltpgsql12 | 6432 | down   | 0.50  | standby | 0  |
false | 0 |   | 
  
| 2020-10-21 11:56:48
(2 rows)


Thanks,
Alan



--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Hot backup in PostgreSQL

[hubert depesz lubaczewski]

On Thu, Oct 22, 2020 at 08:42:08AM +0200, W.P. wrote:
> Hi there,
> 
> how to do "hot backup" (copying files) while database running?
> 
> Not using pg_dump.
> 
> Is there some equivalent of Oracle (I knew v8) "ALTER DATABASE BEGIN
> BACKUP", which froze writes to database files, pushing everything to redo
> files?

There are many ways to do it. To be able to suggest proper solution we'd
need to know:
1. what is the problem with pg_dump?
2. what is the exact problem you're solving (clearly it's not only
   "having backup", as this is done using pg_dump without any problem).

depesz

Re: Hot backup in PostgreSQL

[Paul Förster]

Hi Laurent,

> On 22. Oct, 2020, at 08:42, W.P.  wrote:
> 
> Hi there,
> 
> how to do "hot backup" (copying files) while database running?
> 
> Not using pg_dump.
> 
> Is there some equivalent of Oracle (I knew v8) "ALTER DATABASE BEGIN BACKUP", 
> which froze writes to database files, pushing everything to redo files?

yes, there is. Please read the chapter: 25.3.3.1. Making A Non-Exclusive 
Low-Level Backup

https://www.postgresql.org/docs/current/continuous-archiving.html#BACKUP-LOWLEVEL-BASE-BACKUP

Cheers,
Paul

Hot backup in PostgreSQL

[W.P.]

Hi there,

how to do "hot backup" (copying files) while database running?

Not using pg_dump.

Is there some equivalent of Oracle (I knew v8) "ALTER DATABASE BEGIN 
BACKUP", which froze writes to database files, pushing everything to 
redo files?



Laurent.