recovery_command has precedence over phisical slots?
Hello everyone, I'm experiencing a behaviour I don't really understand if is a misconfiguration or a wanted behaviour: 1) I set up a primary server (a.k.a. db1) with and archive_command to a storage 2) I set up a replica (a.k.a. db2) that created a slot named as slot_2 and that has the recovery_command set to read archived wal on the storage. If I shutdown replica db2 during a pgbench I see the safe_wal_size queried from pg_replication_slots on the primary decrease to a certain amount but still in the max_slot_wal_kepp_size window: even if I restart the replica db2 before the slot_state changes to unreserved or lost I see that the replica gets needed wals from the storage using recovery_command but doesn't use slot on primary. Only if I comment the recovery command on the .conf of the replica then it uses slot. If this is a wanted behaviour I can't understand the need of slots on primary. Hopin' could someone explain me, thanks in advance, Giovanni
Re: recovery_command has precedence over phisical slots?
Il giorno mer 24 ago 2022 alle ore 13:00 Laurenz Albe < laurenz.a...@cybertec.at> ha scritto: > On Wed, 2022-08-24 at 14:18 +0900, Kyotaro Horiguchi wrote: > > At Fri, 19 Aug 2022 18:37:53 +0200, Laurenz Albe < > laurenz.a...@cybertec.at> wrote in > > > On Fri, 2022-08-19 at 16:54 +0200, Giovanni Biscontini wrote: > > > > Hello everyone, > > > > I'm experiencing a behaviour I don't really understand if is a > misconfiguration or a wanted behaviour: > > > > 1) I set up a primary server (a.k.a. db1) with and archive_command > to a storage > > > > 2) I set up a replica (a.k.a. db2) that created a slot named as > slot_2 and that has the recovery_command > > > >set to read archived wal on the storage. > > > > If I shutdown replica db2 during a pgbench I see the safe_wal_size > queried from pg_replication_slots > > > > on the primary decrease to a certain amount but still in the > max_slot_wal_kepp_size window: even > > > > if I restart the replica db2 before the slot_state changes to > unreserved or lost I see that the > > > > replica gets needed wals from the storage using recovery_command but > doesn't use slot on primary. > > > > Only if I comment the recovery command on the .conf of the replica > then it uses slot. > > > > If this is a wanted behaviour I can't understand the need of > slots on primary. > > > > > > This is normal behavior and is no problem. > > > > > > After the standby has caught up using "restore_command", it will > connection to > > > the primary as defined in "primary_conninfo" and stream WAL from there. > > > > The reason that db2 ran recovery beyond the slot LSN is the db2's > > restore_command (I guess) points to db1's archive. If db2 had its own > > archive directory or no archive (that is, restore_command is empty), > > archive recovery stops at (approximately) the slot LSN and replication > > will start from there (from the beginning of the segment, to be > > exact). > > Is it a problem if archive recovery proceeds past the replication slot's > LSN? > > I guess I don't see the problem. > > Yours, > Laurenz Albe > Hi and thanks all, my thoughts: a) if I set up a slot I thought it would be useful for 2 reason: a.1) it has a "per replica" reference on the wal to keep, a.2) after a disconnection in replica (db2) when it reconnects I think it can be quicker to get missing WALs referenced in slot from the primary pg_wal than recover them from archived, especially if archived are on an a S3 bucket (so yes db2 recovery points to the same archive of db1) b) Archive and consequently the recovery command in my thoughts are "the safety" if replica falls behind the wal_keep_size or (in this case) behind the max_slot_wal_keep_size c) I understand that, maybe, the idea behind giving the precedence to to recovery_command is "recovery is present, so don't even give a try to slot because it can be lost so go to "safety" with recovery that is intended to be. but... in this case if I set a slot+a recovery_command the usage and subsequently the risk of filling the disk space, is useless: it uses always the recovery. So if I can say the problem is: I configure a slot that in every case produces more time to set it up, more disk usage, more configuration, but is useless... thanks in advance and best regard, Giovanni p.s. I forgot to specify before: the pg version is 14.5
replication phisical + logical
Hello everyone, on postgresql 14.5 is possible an architecture where: master1 is in physical replication with replica1 and replica2 and replica2 is in logical selective replication with master2? Where on master2 I have db1 who's from logical replication from replica2 and db2 who lives of he's own life and must be enabled to writes. The goal is to set up a preproduction server (master2) where db1 is feeded (maybe at regular times) from a production replica. I considered pgbackrest to derive master2 from a backup of master1 but even if I restore selectively with --db-include=db1 option than the whole master2 is restored: so db2 is removed as template0, template1 and postgres system db are replaced from restore command. Alternative suggestions' will be appreciated. Thanks in advance and best regards, Giovanni -- *Dott. Giovanni Biscontini* * [Divisone Software]* WEB: https://www.es2000.it <http://www.es2000.it/> Privacy e riservatezza: il presente messaggio, così come i relativi allegati, contengono dati ed informazioni da considerarsi strettamente riservate ed è indirizzato esclusivamente al destinatario sopra indicato, il quale è l'unico autorizzato a trattarlo in osservanza delle norme del Regolamento UE 2016/679 (RGPD) . Preghiamo chiunque ricevesse questo messaggio per errore di evitare di copiarlo, divulgarlo, distribuirlo a terzi e di dare notizia al mittente dell’errato invio, distruggendone poi l'eventuale copia cartacea e la copia in formato elettronico. Il titolare dei dati potrà esercitare tutti i diritti di cui all'art.7 del suddetto decreto tra cui quelli di accesso, rettifica, aggiornamento, opposizione al trattamento e cancellazione *Please, print this e-mail only if necessary*
Best Open Source OS for Postgresql
Hello everyone, we're looking for a Open Source alternative to Rhel for our VM server dedicated to Postgresql (14->15) installations. We're testing Alma, Rocky, and Oracle distributions as they're compatible with Rhel package systems. Can you share your experience on a similar choice? Thanks in advance and best regards, Giovanni -- *Cordiali Saluti - Best Regards* *Dott. Giovanni Biscontini* * [Divisone Software]* WEB: https://www.es2000.it <http://www.es2000.it/> Privacy e riservatezza: il presente messaggio, così come i relativi allegati, contengono dati ed informazioni da considerarsi strettamente riservate ed è indirizzato esclusivamente al destinatario sopra indicato, il quale è l'unico autorizzato a trattarlo in osservanza delle norme del Regolamento UE 2016/679 (RGPD) . Preghiamo chiunque ricevesse questo messaggio per errore di evitare di copiarlo, divulgarlo, distribuirlo a terzi e di dare notizia al mittente dell’errato invio, distruggendone poi l'eventuale copia cartacea e la copia in formato elettronico. Il titolare dei dati potrà esercitare tutti i diritti di cui all'art.7 del suddetto decreto tra cui quelli di accesso, rettifica, aggiornamento, opposizione al trattamento e cancellazione *Please, print this e-mail only if necessary*
Re: New to PostgreSQL - looking for query writing tools
Hi Troy, we both use pgAdmin4 and DBeaver (https://dbeaver.io/) Community version but mainly 'cos we need to get in control of other functions of the database server, query is only a part of the job. Another interesting tool is Aquafold (https://www.aquafold.com): Aqua Data Studio is interesting (only tested) but it's a bit pricey... Hope it helps, bye Giovanni Il giorno mer 1 feb 2023 alle ore 18:00 Robin Tang ha scritto: > There's a bunch listed here: > https://wiki.postgresql.org/wiki/PostgreSQL_Clients > > I personally use Postico, but it's only available on Mac. > > On Wed, Feb 1, 2023 at 8:43 AM Troy Sherven wrote: > >> Good morning, >> >> >> >> First of all, I am not a database professional. I work for a small >> company, and a portion of my job includes report writing (crystal, i-Net, >> etc), Excel analysis, etc. We recently migrated to a new ERP that runs on >> a PostgreSQL database. Our previous system was on a MSSQL database. I >> have fairly basic experience writing SQL queries. In the past I would use >> Microsoft SQL Server Management Studio to help in writing my SQL queries >> (I’d use the diagram, criteria, and SQL panes) instead of just writing SQL >> statements. >> >> >> >> I’m looking for recommendations of tools that would work similar to >> Microsoft SQL Server Management Studio. So far I’ve played around with >> pgAdmin (doesn’t seem to have the same query writing abilities) and EMS SQL >> Query (seems more cumbersome than the Microsoft tool). Are there any other >> tools I should be looking at for query writing help? I like to something >> visual and a grid to help write queries. >> >> >> >> Any recommendations would be appreciated! >> >> >> >> Troy >> > -- *Cordiali Saluti* *Dott. Giovanni Biscontini* * [Divisone Software]* *Str. Ponte Alto Sud, 74 41123 Modena (MO)* Phone: 059_452094 Fax: 059_8672171 E-mail: biscontin...@es2000.it Skype: g.biscontini.eurosystem2000 WEB: https://www.es2000.it <http://www.es2000.it/> Privacy e riservatezza: il presente messaggio, così come i relativi allegati, contengono dati ed informazioni da considerarsi strettamente riservate ed è indirizzato esclusivamente al destinatario sopra indicato, il quale è l'unico autorizzato a trattarlo in osservanza delle norme del Regolamento UE 2016/679 (RGPD) . Preghiamo chiunque ricevesse questo messaggio per errore di evitare di copiarlo, divulgarlo, distribuirlo a terzi e di dare notizia al mittente dell’errato invio, distruggendone poi l'eventuale copia cartacea e la copia in formato elettronico. Il titolare dei dati potrà esercitare tutti i diritti di cui all'art.7 del suddetto decreto tra cui quelli di accesso, rettifica, aggiornamento, opposizione al trattamento e cancellazione *Please, print this e-mail only if necessary*
Re: PostgreSQL
Hi Joseph, one way to filter without RLS is using functions to make queries: this way you can cross check input parameters with login user (= user issuing query). Using functions slow an execution but incapsulate query letting you be able even to parse input parameters and filter results. Sorry but I have no time ref for RLS, and remember that before you can use RLS you must enable it on the table you want to use in. best regards, Giovanni Il giorno mar 7 feb 2023 alle ore 12:02 Joseph Kennedy < joseph.kennedy@gmail.com> ha scritto: > I would like restrict access to sensitive or restricted information for > some users (eg. hide data of one or more clients for some database users). > > PostgreSQL allows to create security policy as Row-Level Security, > policies based on the querying user. > > > Are there any other alternatives to RLS, are there any better solutions?? > > What are the advantages and disadvantages of RLS? > > How much RLS will cause a decrease in database performance? > > JK > > > > > -- *Cordiali Saluti* *Dott. Giovanni Biscontini* * [Divisone Software]* *Str. Ponte Alto Sud, 74 41123 Modena (MO)* Phone: 059_452094 Fax: 059_8672171 E-mail: biscontin...@es2000.it Skype: g.biscontini.eurosystem2000 WEB: https://www.es2000.it <http://www.es2000.it/> Privacy e riservatezza: il presente messaggio, così come i relativi allegati, contengono dati ed informazioni da considerarsi strettamente riservate ed è indirizzato esclusivamente al destinatario sopra indicato, il quale è l'unico autorizzato a trattarlo in osservanza delle norme del Regolamento UE 2016/679 (RGPD) . Preghiamo chiunque ricevesse questo messaggio per errore di evitare di copiarlo, divulgarlo, distribuirlo a terzi e di dare notizia al mittente dell’errato invio, distruggendone poi l'eventuale copia cartacea e la copia in formato elettronico. Il titolare dei dati potrà esercitare tutti i diritti di cui all'art.7 del suddetto decreto tra cui quelli di accesso, rettifica, aggiornamento, opposizione al trattamento e cancellazione *Please, print this e-mail only if necessary*
Re: PostgreSQL
Hi, RLS rely on Create policy command: https://www.postgresql.org/docs/15/sql-createpolicy.html it need a table_name, only Il giorno mar 7 feb 2023 alle ore 12:37 Joseph Kennedy < joseph.kennedy@gmail.com> ha scritto: > RLS policy it only affects to command as SELECT, INSERT, UPDATE, DELETE or > can affects to operations on whole database, table like vacuum or reindex > etc ? > > Wiadomość napisana przez Giovanni Biscontini > w dniu 07.02.2023, o godz. 12:16: > > > Hi Joseph, >one way to filter without RLS is using functions to make queries: > this way you can cross check input parameters with login user (= user > issuing query). Using functions slow an execution but incapsulate query > letting you be able even to parse input parameters and filter results. > Sorry but I have no time ref for RLS, and remember that before you can use > RLS you must enable it on the table you want to use in. > best regards, Giovanni > > Il giorno mar 7 feb 2023 alle ore 12:02 Joseph Kennedy < > joseph.kennedy@gmail.com> ha scritto: > >> I would like restrict access to sensitive or restricted information for >> some users (eg. hide data of one or more clients for some database users). >> >> PostgreSQL allows to create security policy as Row-Level Security, >> policies based on the querying user. >> >> >> Are there any other alternatives to RLS, are there any better solutions?? >> >> What are the advantages and disadvantages of RLS? >> >> How much RLS will cause a decrease in database performance? >> >> JK >> >> >> >> >> > >
Re: pgbouncer
On the DB server: the bouncer is made to optimize many and fast connections to the DB and fasten the throughput, so You've to have the shortest way to go to the data. One important thing: remember that different user raise different connections so be careful to choose which application you'll direct to pgbouncer. A couple more: be careful to choose the right pool_mode and tune the right server_connect_timeout value. all the best, Giovanni Il giorno mer 28 giu 2023 alle ore 13:19 Rita ha scritto: > > seems like I may need to deploy pgbouncer for my webapp. should i deploy > it on the db server or on the webserver? > -- > --- Get your facts first, then you can distort them as you please.-- > -- *Best Regards* *Dott. Giovanni Biscontini* Software Developer & Database Architect Web: https://www.es2000.it <http://www.es2000.it/> _ Privacy e riservatezza: il presente messaggio, così come i relativi allegati, contengono dati ed informazioni da considerarsi strettamente riservate ed è indirizzato esclusivamente al destinatario sopra indicato, il quale è l'unico autorizzato a trattarlo in osservanza delle norme del Regolamento UE 2016/679 (RGPD). Chiunque ricevesse questo messaggio per errore di evitare di copiarlo, divulgarlo, distribuirlo a terzi e di dare notizia al mittente dell’errato invio, distruggendo poi l'eventuale copia cartacea e la copia in formato elettronico.
pg_agent jobs
Hello all members, here's my question: db version 14.5 we have to create a pg_agent job with 2 steps: 1_step execute query: if query result is true or find > 0 records then process next step, else it stops executing job 2_step execute batch command we tried to seta as 1_step the query: SELECT EXISTS (SELECT * FROM mytable WHERE someconditions) but even if it returns false 2_step is always executed. Need some help, thanks in advance, Giovanni -- best regards
Re: pg_agent jobs
Hi, Yes I know I've to use the "on error" property, but the cue is to raise an error if query has a false result: for my knowledges the only way to do it is to "create or replace" a PL/pgSQL procedure that than uses a "RAISE ERROR" inside, but isn't there a more "SQL only way to do it? thanks in advance, G Il giorno ven 29 set 2023 alle ore 18:15 Adrian Klaver < adrian.kla...@aklaver.com> ha scritto: > On 9/29/23 08:55, Giovanni Biscontini wrote: > > Hello all members, here's my question: db version 14.5 > > we have to create a pg_agent job with 2 steps: > > 1_step execute query: if query result is true or find > 0 records then > > process next step, else it stops executing job > > 2_step execute batch command > > we tried to seta as 1_step the query: > > SELECT EXISTS (SELECT * FROM mytable WHERE someconditions) > > but even if it returns false 2_step is always executed. > > Need some help, thanks in advance, Giovanni > > The only thing I could see here: > > https://www.pgadmin.org/docs/pgadmin4/development/pgagent_jobs.html > > that might work is: > > " > Use the On error drop-down to specify the behavior of pgAgent if it > encounters an error while executing the step. Select from: > > Fail - Stop the job if you encounter an error while processing this > step. > > Success - Mark the step as completing successfully, and continue. > > Ignore - Ignore the error, and continue. > " > > So in the first step induce an error if the conditions are not met. > > > > -- > > > > best regards > > > > > > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > > -- *Cordiali saluti* *Dott. Giovanni Biscontini* Software Developer & Database Architect Strada Ponte Alto Sud, 74 41123 Modena (MO) Tel.: 059.452094 Cell: 3938282174 Mail: biscontin...@es2000.it Web: https://www.es2000.it <http://www.es2000.it/> _ Privacy e riservatezza: il presente messaggio, così come i relativi allegati, contengono dati ed informazioni da considerarsi strettamente riservate ed è indirizzato esclusivamente al destinatario sopra indicato, il quale è l'unico autorizzato a trattarlo in osservanza delle norme del Regolamento UE 2016/679 (RGPD). Chiunque ricevesse questo messaggio per errore di evitare di copiarlo, divulgarlo, distribuirlo a terzi e di dare notizia al mittente dell’errato invio, distruggendo poi l'eventuale copia cartacea e la copia in formato elettronico.
Re: PostgreSQL C++ Interface version 14.9 using static library instead of shared library
Hi, maybe you're not compiling with ssl enabled (using --with-openssl) ? regards, Giovanni Il giorno ven 20 ott 2023 alle ore 12:58 M Tarkeshwar Rao < m.tarkeshwar@ericsson.com> ha scritto: > Hi > > While compiling PostgreSQL C++ Interface version 14.9 . Found that > libpq.so is getting created with libssl.a and libcrypto.a instead of shared > library libssl.so and libcrypto.so. > It is created like below: > *user@seli[postgres/14.9/lib]$* ldd > libpq.so > linux-vdso.so.1 (0x7ffe67fed000) > libpthread.so.0 => /lib64/libpthread.so.0 (0x7efcf11b3000) > libc.so.6 => /lib64/libc.so.6 (0x7efcf0df8000) > /lib64/ld-linux-x86-64.so.2 (0x7efcf1b26000) > However it should use ssl and crypto as shared library like following: > > Same when we compiled for 14.8, Its ldd out shows following: > *user@seli[postgres/14.8/lib]$* ldd > libpq.so > linux-vdso.so.1 (0x7ffcf715d000) > libssl.so.3 => > /scm/FE/SW_3pp_cm/em21_9/sles/15/openssl/3.1.2/lib64/libssl.so.3 > (0x7f23066f8000) > libcrypto.so.3 => > /scm/FE/SW_3pp_cm/em21_9/sles/15/openssl/3.1.2/lib64/libcrypto.so.3 > (0x7f2305fb3000) > libpthread.so.0 => /lib64/libpthread.so.0 (0x7f2305d94000) > libc.so.6 => /lib64/libc.so.6 (0x7f23059d9000) > libdl.so.2 => /lib64/libdl.so.2 (0x7f23057d5000) > /lib64/ld-linux-x86-64.so.2 (0x7f2306bee000) > > Any suggestion and feedback , why it is taking static library instead of > shared object ? > *Also there is the size difference of libpq.so. in 14.8 it is around 320 > kb and in 14.9 it is around 6 mb**.* > > Thanks > Tarkeshwar > > > > > > > -- *Cordiali saluti* *Dott. Giovanni Biscontini* Software Developer & Database Architect Strada Ponte Alto Sud, 74 41123 Modena (MO) Tel.: 059.452094 Cell: 3938282174 Mail: biscontin...@es2000.it Web: https://www.es2000.it <http://www.es2000.it/> _ Privacy e riservatezza: il presente messaggio, così come i relativi allegati, contengono dati ed informazioni da considerarsi strettamente riservate ed è indirizzato esclusivamente al destinatario sopra indicato, il quale è l'unico autorizzato a trattarlo in osservanza delle norme del Regolamento UE 2016/679 (RGPD). Chiunque ricevesse questo messaggio per errore di evitare di copiarlo, divulgarlo, distribuirlo a terzi e di dare notizia al mittente dell’errato invio, distruggendo poi l'eventuale copia cartacea e la copia in formato elettronico.
