Re: Streaming Replication between PostGreSQL 9.2.2 on Red Hat and PostGreSQL 9.2.24 on Debian
Hi, - Doing Streaming Replication between different minor version of PG is possible but not recommended [2] - Doing Streaming Replication between different OSes is not recommended pre ICU (pg10), please check you glibc versions. [1] [1] https://www.postgresql.org/message-id/ba6132ed-1f6b-4a0b-ac22-81278f5ab...@tripadvisor.com [2] https://www.postgresql.org/docs/9.2/static/warm-standby.html (planning section) Hoep this helps. Cheers Ben 2018-05-22 10:13 GMT+02:00 Jonatan Evald Buus < jonatan.b...@cellpointmobile.com>: > Thanks Ian, thank you for pointing out the obvious. > It appears that Debian's *pg_dropcluster* command had unexpected > consequences you live, you pull out your hair in frustration and you > learn. > > I now have streaming replication working as we expected, can you confirm > that my *(somewhat unusual scenario?) *shouldn't cause any problems? > Specifically: > - Using Streaming Replication between two PostGreSQL instances with > different minor versions will work *(9.2.2 on RHEL and 9.2.24 on Debian)* > - Using Streaming Replication between two servers running different > operating systems *(RHEL 5.5 and Debian 8 / 9) *will work > > Greatly appreciate your insight > > Cheers > Jona > > On 21 May 2018 at 13:27, Ian Barwick wrote: > >> On 05/21/2018 07:18 PM, Jonatan Evald Buus wrote: >> > Hi there, >> > >> > I'm trying to configure streaming replication between a Red Hat server >> > running PostGreSQL 9.2.2 and a Debian server running PostGreSQL 9.2.24 >> > with Hot Standby enabled. >> > >> > While the base backup works fine using /pg_basebackup/, I get the >> following >> > errors with hot standby enabled on the Debian slave when starting >> PostGreSQL: >> >> > /WARNING: WAL was generated with wal_level=minimal, data may be >> missing/ >> > /HINT: This happens if you temporarily set wal_level=minimal without >> taking a new base backup./ >> > /FATAL: hot standby is not possible because wal_level was not set to >> "hot_standby" on the master server/ >> > /HINT: Either set wal_level to "hot_standby" on the master, or turn >> off hot_standby here/ >> > >> > If I turn Hot Standby off on the Debian Slave I get the following >> errors: >> > /FATAL: database system identifier differs between the primary and >> standby/ >> > /DETAIL: The primary's identifier is 5940475598986796885, the >> standby's identifier is 6557962695089036503./ >> >> The standby clearly hasn't been cloned from the primary, otherwise the >> identifiers would be the same. Are you sure the PostgreSQL instance >> running on the standby is the one you backed up with pg_basebackup? >> >> >> Regards >> >> Ian Barwick >> >> -- >> Ian Barwick http://www.2ndQuadrant.com/ >> PostgreSQL Development, 24x7 Support, Training & Services >> > > > > -- > Jonatan Evald Buus > CTO, CellPoint Mobile > www.cellpointmobile.com > *WE MAKE TRAVEL EASIER™* > > O: +45 70211512 <+45%2070%2021%2015%2012> | M: +45 28882861 > <+45%2028%2088%2028%2061> > E: jonatan.b...@cellpointmobile.com > *Copenhagen* | Dubai | London | Miami | Pune | Singapore >
Re: Setting up continuous archiving
Hi, I am not sure what you call discrete / continuous. pgbackrest allows backups of different kinds: full, incremental and differential. It keeps the wals necessary to recover since the oldest backup until the current time. The retention is expressed in number of full backups. You can also specify a number of differential backups. You have the choice to keep the wal necessary to restore to the end of the backup in the backup directory itself in addition to the archive directory. If you use this method (parameter archive-copy) the backup is "standalone" and you can copy it out of the pgbackrest backup repository and keep it forever. Le lun. 15 oct. 2018 à 12:31, Yuri Kanivetsky a écrit : > pgBackRest doesn't seem to allow the latter: recovery to any point in > time, only to some discrete moments. Correct me if I'm wrong. > Are you talking about PITR ? https://pgbackrest.org/user-guide.html#pitr Is that doable with both of them (pgBackRest, Barman)? Does it make > sense to use repmgr with pgBackRest? > It's doable but remgr and barman are supposed to work together more seemlessly since they re both products of 2ndQ. But does it make sense to use repmgr ? My opinion: I use pgbackrest for three years now and am very happy with it. My choice was made based on personal preference, features (at the time there was more discrepencies) and I prefered the overall design. I use corosync & pacemaker with PAF for HA so I never had to use repmgr. Benoit.
Hash aggregate spilling (v13) / partitions & batches
Hi,
I am testing things on hash aggregate spilling in version 13.1 and am
struggling to understand the partition thing in the two explains below.
My understanding is that a partition corresponds to a spill file which will
be treated in a later batch (which can re-spill in some cases).
Am I right to think that the second explain analyze says that PostgreSQL
was planning for 8 batches (there are 8 planned partitions) and that only
one was necessary (= no spill files) ?
regards
benoit
[local]:5433 postgres@postgres=# CREATE TABLE tableA(ac1 int, ac2 int);
CREATE TABLE
[local]:5433 postgres@postgres=# CREATE TABLE tableB(bc1 int, bc2 int);
CREATE TABLE
[local]:5433 postgres@postgres=# INSERT INTO tableA SELECT x, random()*100
FROM generate_series(1,100) AS F(x);
INSERT 0 100
[local]:5433 postgres@postgres=# INSERT INTO tableB SELECT mod(x,10),
random()*100 FROM generate_series(1,100) AS F(x);
INSERT 0 100
[local]:5433 postgres@postgres=# SELECT name, setting, unit FROM
pg_settings WHERE name IN('work_mem', 'hash_mem_multiplier');
name | setting | unit
-+-+--
hash_mem_multiplier | 1 | NULL
work_mem| 4096| kB
(2 rows)
[local]:5433 postgres@postgres=# EXPLAIN (ANALYZE) SELECT ac1, count(ac2),
sum(bc2) FROM tableA INNER JOIN TABLEB ON ac1 = bc1 GROUP BY Ac1;
QUERY PLAN
---
HashAggregate (cost=137290.50..157056.12 rows=100 width=20) (actual
time=773.405..889.020 rows=9 loops=1)
Group Key: tablea.ac1
Planned Partitions: 32 Batches: 33 Memory Usage: 4369kB Disk Usage:
30456kB
-> Hash Join (cost=30832.00..70728.00 rows=100 width=12) (actual
time=158.774..583.031 rows=90 loops=1)
Hash Cond: (tableb.bc1 = tablea.ac1)
-> Seq Scan on tableb (cost=0.00..14425.00 rows=100 width=8)
(actual time=0.023..77.297 rows=100 loops=1)
-> Hash (cost=14425.00..14425.00 rows=100 width=8) (actual
time=158.378..158.379 rows=100 loops=1)
Buckets: 131072 Batches: 16 Memory Usage: 3471kB
-> Seq Scan on tablea (cost=0.00..14425.00 rows=100
width=8) (actual time=0.010..53.476 rows=100 loops=1)
Planning Time: 0.824 ms
Execution Time: 895.251 ms
(11 rows)
[local]:5433 postgres@postgres=# SET hash_mem_multiplier TO 5;
SET
[local]:5433 postgres@postgres=# EXPLAIN (ANALYZE) SELECT ac1, count(ac2),
sum(bc2) FROM tableA INNER JOIN TABLEB ON ac1 = bc1 GROUP BY Ac1;
QUERY PLAN
---
HashAggregate (cost=137290.50..157056.12 rows=100 width=20) (actual
time=696.684..714.198 rows=9 loops=1)
Group Key: tablea.ac1
Planned Partitions: 8 Batches: 1 Memory Usage: 15633kB
-> Hash Join (cost=30832.00..70728.00 rows=100 width=12) (actual
time=171.789..560.692 rows=90 loops=1)
Hash Cond: (tableb.bc1 = tablea.ac1)
-> Seq Scan on tableb (cost=0.00..14425.00 rows=100 width=8)
(actual time=0.032..78.718 rows=100 loops=1)
-> Hash (cost=14425.00..14425.00 rows=100 width=8) (actual
time=168.592..168.593 rows=100 loops=1)
Buckets: 524288 Batches: 4 Memory Usage: 13854kB
-> Seq Scan on tablea (cost=0.00..14425.00 rows=100
width=8) (actual time=0.018..52.796 rows=100 loops=1)
Planning Time: 0.242 ms
Execution Time: 717.914 ms
(11 rows)
Re: pg9.6 when is a promoted cluster ready to accept "rewind" request?
Hi, You might have to wait for pg_is_in_recovery to be false after the promotion. (in 9.6 pg_ctl promote doesn't wait for promotion to complete unlike 10). [1] You should CHECKOINT between 2 and 3. (or wait for the first checkpoint to finish) In the thread [2], Michael Paquier explains that: " This makes the promoted standby update its timeline number in the on-disk control file, which is used by pg_rewind to check if a rewind needs to happen or not. " Benoit, [1] https://paquier.xyz/postgresql-2/postgres-10-wait-pgctl-promote/ [2] https://www.postgresql.org/message-id/flat/20180804205026.GJ20967%40paquier.xyz#f89ffda99fb5e7355e7499f496a712f7
Gist fastbuild and performances
Hi, I am playing around with the gist fast build and comparing the result between v13 and v14. The space gain and speed increase are really awesome. When I compare the performance with the following script, I get a lot more data read into the buffers in v14 and a little slower query. Is it expected ? (is the test dumb / too artificial ?) (I found some discussion about the buffer usage but don't quite understand the outcome https://www.postgresql.org/message-id/08173bd0-488d-da76-a904-912c35da446b%40iki.fi ) The script : --pg14 \timing on CREATE TABLE gist_fastbuild AS SELECT point(random(),random()) as pt FROM generate_series(1,1000,1); CREATE INDEX ON gist_fastbuild USING gist (pt); VACUUM ANALYZE gist_fastbuild; \di+ gist_fastbuild_pt_idx EXPLAIN (ANALYZE, BUFFERS) SELECT pt FROM gist_fastbuild WHERE pt <@ box(point(.5,.5), point(.75,.75)); COPY gist_fastbuild TO '/tmp/gist_fastbuild.copy'; --pg13 \timing on CREATE TABLE gist_fastbuild(pt point); COPY gist_fastbuild FROM '/tmp/gist_fastbuild.copy'; CREATE INDEX ON gist_fastbuild USING gist (pt); VACUUM ANALYZE gist_fastbuild; \di+ gist_fastbuild_pt_idx EXPLAIN (ANALYZE, BUFFERS) SELECT pt FROM gist_fastbuild WHERE pt <@ box(point(.5,.5), point(.75,.75)); The explains : V14# EXPLAIN (ANALYZE, BUFFERS) SELECT pt FROM gist_fastbuild WHERE pt <@ box(point(.5,.5), point(.75,.75)); QUERY PLAN - Index Only Scan using gist_fastbuild_pt_idx on gist_fastbuild (cost=0.42..419.42 rows=1 width=16) (actual time=0.350..129.309 rows=626005 loops=1) Index Cond: (pt <@ '(0.75,0.75),(0.5,0.5)'::box) Heap Fetches: 0 Buffers: shared hit=303083 Planning: Buffers: shared hit=13 Planning Time: 0.454 ms Execution Time: 148.611 ms (8 rows) V13# EXPLAIN (ANALYZE, BUFFERS) SELECT pt FROM gist_fastbuild WHERE pt <@ box(point(.5,.5), point(.75,.75)); QUERY PLAN - Index Only Scan using gist_fastbuild_pt_idx on gist_fastbuild (cost=0.42..539.42 rows=1 width=16) (actual time=0.523..107.393 rows=626005 loops=1) Index Cond: (pt <@ '(0.75,0.75),(0.5,0.5)'::box) Heap Fetches: 0 Buffers: shared hit=17334 Planning: Buffers: shared hit=13 Planning Time: 0.396 ms Execution Time: 126.713 ms (8 rows)
Re: Gist fastbuild and performances
Hi, thanks for the answer. I ran the same scipt on both sides at first. Then I switched to a COPY because the CREATE TABLE generates random data. Since I got weird results, I wanted to be sure I had the same data on both versions. I ran the tests several times (and even asked a collegue to do it on his laptop) with the same results. I totally agree that the query time are small and difficult to compare (but they are consistent across runs). I am just surprised that we have to access x17 pages for the same result on version 14. On bigger queries it could count. I just wanted to know if it's a know tradeoff of this new feature.
Re: Gist fastbuild and performances
On Fri, Oct 8, 2021 at 4:33 PM Francisco Olarte wrote: > If you do not run the same sequences, you do not know. Note I do not > know what exact sequences you have tested, I write with only what I > have read as as input. > I ran this on both versions on fresh instances / clusters with the data from a previous run : \timing on CREATE TABLE gist_fastbuild(pt point); COPY gist_fastbuild FROM '/tmp/gist_fastbuild.copy'; CREATE INDEX ON gist_fastbuild USING gist (pt); VACUUM ANALYZE gist_fastbuild; \di+ gist_fastbuild_pt_idx EXPLAIN (ANALYZE, BUFFERS) SELECT pt FROM gist_fastbuild WHERE pt <@ box(point(.5,.5), point(.75,.75)); I get the same results as before.
