Re: PG 9.2 slave restarted - cache not impacted

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

PG 9.2 slave restarted - cache not impacted

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

Re: Hot backup in PostgreSQL

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

postgres materialized view refresh performance

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

Re: Hot backup in PostgreSQL

> 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

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

"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

Re: Hot backup in PostgreSQL

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

Re: How to get debuginfo from building source code

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

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

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 >

Initplan placed at the righttree or the lefttree of joinnode

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

Re: Hot backup in PostgreSQL

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

Re: Setup Pgpool2 with Postgresql Streaming Replication

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

Re: Hot backup in PostgreSQL

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

Setup Pgpool2 with Postgresql Streaming Replication

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:

Re: Hot backup in PostgreSQL

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

Re: Hot backup in PostgreSQL

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

Hot backup in PostgreSQL

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.