Re: WAL replay issue from 9.6.8 to 9.6.10
Hi, 2018-08-29 6:02 GMT+02:00 Dave Peticolas : > Hello, I'm seeing some issues with WAL replay on a test server running > 9.6.10 using WAL archived from a 9.6.8 primary server. It reliably PANICs > during replay with messages like so: > > WARNING: page 1209270272 of relation base/16422/47496599 does not exist > CONTEXT: xlog redo at 4810/C84F8A0 for Btree/DELETE: 88 items > PANIC: WAL contains references to invalid pages it looks like you are hitting pretty much the same problem as I: https://www.postgresql.org/message-id/flat/153492341830.1368.3936905691758473953%40wrigleys.postgresql.org The only major difference, you are restoring from the backup, while in my case the host running replica has crashed. Also in my case, the primary was already running 9.6.10. In my case, it also panics during "Btree/DELETE: XYZ items" and page number of relation is insanely huge.
Re: Highly academic: local etcd & Patroni Cluster for testing on a single host
Hi,
On Wed, 26 Feb 2020 at 08:55, Paul Förster wrote:
> no, with Patroni, replicas are always initiated by Patroni. Patroni copies
> the whole PGDATA including everything (postgresql.conf, etc.) in it to the
> replica site. When launching Patroni for the first time, all you need is its
> yaml configuration file and an empty PGDATA. It then will copy the whole
> master's PGDATA as is, launch the replica database cluster and start
> replication.
That's not correct, Patroni will happily pick up the existing data directory.
> Even if Patroni uses pg_basebackup internally (which I assume it does), there
> is no way to pass parameters to it.
This is also not correct. One can specify arbitrarily parameters for
pg_basebackup in the Patroni config file:
postgresql:
basebackup:
tablespace-mapping: /foo=/bar
waldir: /my/waldir
Regards,
--
Alexander Kukushkin
Re: Unable to start replica after failover
Hi, On Tue, 23 Aug 2022 at 16:31, Lahnov, Igor wrote: > > Our 'restore_command' on *previous leader* restores a partial file from > archive (from *new leader*) > > -> > 2022-05-23 01:50:14 [123730]: [1-1]: INFO: pg_probackup archive-get WAL > file: 0002054E00FB, remote: ssh, threads: 1/1, batch: 1 > <- > > Does it really restore the *partial* file? And why would it do that? The old primary didn't have a chance to archive 0002054E00FB, the new primary archived it as 0002054E00FB.partial after promotion. Postgres is requesting the 0002054E00FB file, but somehow gets a 0002054E00FB.partial instead. Why? IMO, pg_probackup does something weird. Regards, -- Alexander Kukushkin
Re: Unable to start replica after failover
Hi, On Wed, 24 Aug 2022 at 13:37, Lahnov, Igor wrote: > > > Yes, Postgres asks for 0002054E00FB and gets renamed > 0002054E00FB.partial (without *partial* postfix). > But why? This is totally weird and unexpected behavior. Why pg_probackup is doing this? Regards, -- Alexander Kukushkin
Re: Unable to start replica after failover
Hi Igor, On Fri, 26 Aug 2022 at 13:43, Lahnov, Igor wrote: > I can't answer your question. What do you think, the recovery from the > archive should work in this case? > > 1. the partial file should not be restored at all cases > > 2. the partial file should recover as a partial and replace the local > partial > > 3. recovery command, should return a conflict - file already exists > The partial file should never be restored automatically. I have no clue why pg_probackup developers decided to do it. Postgres never requests the partial file using the restore_command, instead, it requests a file from the next timeline. Regards, -- Alexander Kukushkin
Re: Unable to start replica after failover
On Tue, Sep 6, 2022, 08:46 Lahnov, Igor wrote: > What do you think it is possible to add a check to the restore command, > that a partial or full file already exists? > > Or is disabling the restore command a possible solution in this case? > My opinion didn't change, pg_probackup does a weird thing. It shouldn't restore the partial file instead of the normal when the normal file doesn't exists. Regards, -- Alexander Kukushkin >
Re: Patroni, slots, and expiring WALs
Hi, On Tue, 28 Mar 2023 at 16:55, Peter J. Holzer wrote: > > However, when we took down one node for about two hours for some tests > recently (with some moderate traffic on the remaining node), the replica > didn't catch up after being restarted and inspection of the logs showed > that it was trying to get WALs which had already been deleted. > It is impossible to know whether it is down temporarily or gone forever, therefore the slot is removed as soon as the member key expires (after TTL). > So apparently, `use_slots: true` isn't enough. What else do I have to > configure? (I know about wal_keep_size, but it was my understanding that > this isn't needed when slots are used) > The best option is to configure continuous archiving and PITR. Backups are always important. The second option - you can put all member names into permanent slots configuration (using patronictl edit-config): slots: nodename1: type: physical nodename2: type: physical nodename3: type: physical This way slots representing these members will not be removed. Regards, -- Alexander Kukushkin
Re: Patroni vs pgpool II
Hi, On Wed, 5 Apr 2023 at 01:01, Tatsuo Ishii wrote: > Hi, > > I am not sure if Patroni provides load balancing feature. > It depends on understanding of load-balancing: - If we talk about load balancing read-only traffic across multiple replicas - it is very easy to achieve with Patroni. - If we talk about parsing all queries in order to figure out whether they are read-write or read-only, then no. BUT, even if there is a solution that parses queries to make a decision it I would not recommend anyone to use it unless all consequences are understood. Specifically, not every read-only query could be salefy sent to a replica, because they could be lagging behind the primary. Only application (developers) could decide whether for a specific query they could afford slightly outdated results. Most of the popular application frameworks support configuring two connection strings for this purpose. Regards, -- Alexander Kukushkin
Re: pg_checksums?
On Mon, 30 Oct 2023 at 14:46, Ron wrote: > Erroneously thinking that Percona develops Patroni. :D > IIRC, they may have made one or two contributions, but very minor. But anyway, Patroni is orthogonal to pg_checksums. As Michael already said, the following workflow works just fine (I did it dozens of times): 1. enable checksums on the standby node 2. start the standby and let it catch up with the primary 3. switchover to a standby node 4. enable checksums on the former primary (now replica). Regards, -- Alexander Kukushkin
Re: pg_checksums?
On Mon, 30 Oct 2023, 19:34 Paul Förster, wrote: > > > Just to be sure I understand you correctly: This does not work with > Patroni? > That's not what I said. Patroni only manages Postgres. It is exactly the same Postgres as you would run it without Patroni. Everything will work. Regards, -- Alexander Kukushkin >
Re: Is there any technical reason why "alter table .. set not null" can't use index?
Hi, On Wed, 8 Sep 2021, 06:59 hubert depesz lubaczewski, wrote: > Hi, > we needed recently to add not null constraint on some fields, and it > struck me that it took long. > Obviously - pg has to check the data. But it seems that it can't use > index. > It can't use the index, but can use an already existing CHECK CONSTRAINT, that could be created as NOT VALID and validated without holding heavy locks. After adding not null you can drop the constraint. Regards, -- Alexander Kukushkin
Re: Using Token (JWT) authentication mechanism in Postgres
Hi, 2018-01-24 22:27 GMT+01:00 Julio Cesar Tenganan Daza : > Hello, > > > > I would like to know if is possible to use Token (JWT) authentication > mechanism in Postgres? In order to authenticate users and also authorize > access to specific tables, This is in a multi-tenant application context > where users can create their own tables and share it if they want. > > > > Is it possible this authentication mechanism or is there any plugin to > achieve it? > Postgres can use pam for authentication. I am not sure that such plugin already exists, but it shouldn't be very hard to implement it. There are a few problems though: 1. JWT token already contains information about username, but you still have to provide it (username) when opening connection. 2. Token has to be send as a connection password. Therefor connection must be encrypted. 3. Usually JWT tokens are quite big in size, but for example when psql is asking you for a password, it thinks that password can't be longer than 100 characters. And this value is hard-coded. It's possible to overcome this issue if you specify your token in PGPASSWORD env variable. We at Zalando are using JWT tokens to authenticate employees when they are accessing postgres databases, but we are not dealing with JWT directly. We have some OAuth infrastructure in-place, which can validate JWT tokens. At the end it boiled down to sending http request to tokeninfo service and validating its answer. Source code of PAM module is here: https://github.com/CyberDem0n/pam-oauth2 Basically you can do something similar. Either take pam-oauth2 as a reference and add possibility to validate JWT tokens or implement your tokeninfo service. > > > Thank You for your help! > > > > Regards, > > > > Cesar > Regards, -- Alexander Kukushkin
Re: Using Token (JWT) authentication mechanism in Postgres
Hi, > > And also I have an additional question, can the implemented PAM > authentication module be used from JDBC connections? Or they works totally > apart? > I think it should work from JDBC without any additional effort. And basically pam module itself doesn't even know that it is used from postgres. Regards, -- Alexander Kukushkin
RE: PostgreSQL Active-Active
I would recommend to read https://linode.com/docs/databases/postgresql/create-a-highly-available-postgresql-cluster-using-patroni-and-haproxy/ And actually we just did a 3 hours tutorial at pgconf.ru and soon we will publish slides. In April we will give the Patroni tutorial at postgresconf.us On 7 Feb 2018 17:29, "Lazaro Garcia" wrote: Do you have any tutorial about patroni + ha-proxy with postgresql (what about load balancing with this setup?) Regards. -Mensaje original- De: Stephen Frost [mailto:sfr...@snowman.net] Enviado el: martes, 6 de febrero de 2018 06:12 a. m. Para: Dhandapani Shanmugam CC: pgsql-admin; pgsql-gene...@postgresql.org Asunto: Re: PostgreSQL Active-Active Greetings, * Dhandapani Shanmugam (postgresq...@gmail.com) wrote: > Do we have active-active setup in postgreSQL, like we have in oracle RAC?. > Sorry if this question has been addressed already. What will be best > open source HA for postgresql with zero RTO and RPO. What you're probably looking for is a 3 (or more) node setup with patroni, etcd, and synchronous replication. Note that this will be a shared-nothing architecture, avoiding the risk that something bad happens with the storage system but requiring that the data be replicated multiple times. Using synchronous replication means that you have to have the nodes all close to each other, of course, but meets the zero RPO. Zero RTO is just silly, but with patroni and haproxy you can detect and failover pretty darn fast. Thanks! Stephen Regard, Alexander Kukushkin
