In certain cases, can UPDATE transactions fail rather than block waiting for “FOR UPDATE lock”?
I'm using Postgres 9.6.5. In the docs under-- [13.3. Explicit Locking][ https://www.postgresql.org/docs/9.6/static/explicit-locking.html] "13.3.2. Row-level Locks" -> "Row-level Lock Modes" -> "FOR UPDATE": ''' FOR UPDATE causes the rows retrieved by the SELECT statement to be locked as though for update. This prevents them from being locked, modified or deleted by other transactions until the current transaction ends. That is, other transactions that attempt UPDATE, DELETE, SELECT FOR UPDATE, SELECT FOR NO KEY UPDATE, SELECT FOR SHARE or SELECT FOR KEY SHARE of these rows will be blocked until the current transaction ends; conversely, SELECT FOR UPDATE will wait for a concurrent transaction that has run any of those commands on the same row, and will then lock and return the updated row (or no row, if the row was deleted). ... The mode is also acquired by any DELETE on a row, and also by an UPDATE that modifies the values on certain columns. Currently, the set of columns considered for the UPDATE case are those that have a unique index on them that can be used in a foreign key (so partial indexes and expressional indexes are not considered), but this may change in the future. ''' Regarding UPDATEs on rows that are locked via "SELECT FOR UPDATE" in another transaction, I read the above as follows: other transactions that attempt UPDATE of these rows will be blocked until the current transaction ( which did "SELECT FOR UPDATE" for those rows ) ends, unless the columns in these rows being UPDATE'ed are those that don't have a unique index on them that can be used in a foreign key. Is this correct ? If so, if I have a table "program" with a text column "stage" ( this column doesn't fit "have a unique index on them that can be used in a foreign key" ), and I have a transaction that does "SELECT FOR UPDATE" for some rows followed by UPDATE'ing "stage" in these rows, is it correct that other concurrent transactions doing "UPDATE" on these rows can fail, rather than block until the former transaction ends ? pk
Calculate hardware requirements
Hello All, I'm looking for hardware configurations to set up 1 master and 2 hot-standby slaves using 9.6 in one DC. Also, I need to have DR with the same setup with cross-site replication enabled. I went through a lot of docs/blogs suggesting 4cores and at least 4/8GB RAM. But I'm looking for help on how exactly one can justify the hardware requirements, like a formula ? Please advise. Regards, PK
Re: Calculate hardware requirements
Thank you for the detailed reply. I understand my question is generic. But just thought if I can get some good place to start. I will look into the suggestions you made. On Thu, Jun 4, 2020 at 4:02 PM Fabio Pardi wrote: > Hi, > > in my opinion your question is too generic to get an accurate answer. To > educate yourself reading Postgres docs or some good books would be in my > opinion the best way to give an answer yourself to your own question. Then > you can still post to the ML on some specific setting (postgres performance > ML is the best place). > > Much of the requirements depends on the expected load on the database and > what kind of usage you will do, such as OLTP or DWH/BI. Also the database > size is important to fit in the picture. > > As rule of thumb, you want all your installations to be identical in terms > of hardware specs. CPU should be able to serve your queries and your > clients, so you must have enough cores to serve the expected number of > connections without degrading performances. > > About RAM, the more the better, but if you have enough to fit your db (or > the part you use of your db) in RAM, you will probably avoid many of your > problems about disks performances. > > Do not forget disks, RAID controllers, networking, SLA, SLO, HA, DR.. > > OT: I would use newer Postgres than 9.6 if I were you, unless you have > good reasons to use 9.6. > > > regards, > > fabio pardi > > > > > On 04/06/2020 11:36, Praveen Kumar K S wrote: > > Hello All, > > I'm looking for hardware configurations to set up 1 master and 2 > hot-standby slaves using 9.6 in one DC. Also, I need to have DR with the > same setup with cross-site replication enabled. I went through a lot of > docs/blogs suggesting 4cores and at least 4/8GB RAM. But I'm looking for > help on how exactly one can justify the hardware requirements, like a > formula ? Please advise. > > Regards, > PK > > > -- *Regards,* *K S Praveen KumarM: +91-9986855625 *
[HELP] query regarding replication
Hello, I had setup one master and hot-slave by setting WAL_LEVEL=replica But I use a debezium plugin and it requires to connect to master with WAL_LEVEL=logical and it is mandatory. Can I achieve master/slave streaming replication by setting WAL_LEVEL to logical on master ? Are there any drawbacks of it ? -- *Regards,* *K S Praveen Kumar*
Re: [HELP] query regarding replication
Thanks. Will this approach replicate DDL changes ? On Tue, Jun 9, 2020 at 1:36 PM Andreas Kretschmer wrote: > > > Am 09.06.20 um 09:55 schrieb Praveen Kumar K S: > > Can I achieve master/slave streaming replication by setting WAL_LEVEL > > to logical on master ? Are there any drawbacks of it ? > > yes, no problem. the wal's would be a bit larger, that's all. > > > Regards, Andreas > > -- > 2ndQuadrant - The PostgreSQL Support Company. > www.2ndQuadrant.com > > > > -- *Regards,* *K S Praveen KumarM: +91-9986855625 *
[HELP] Reset postgres server
Hello, I have a master and slave setup running and sometimes we have to do a complete refresh on dev environments. How can I reset postgres server so that it wipes out everything including WAL ? So that I configure master/slave streaming replication again from scratch ? TIA. -- *Regards,* *K S Praveen Kumar*
Re: [HELP] Reset postgres server
Providing more details. OS is Ubuntu 16.04 and postgres is installed from APT. logical and physical replication slots configured on master. No WAL archiving configured. Streaming replication is enabled on master. Slave is hot-standby On Wed, Jun 10, 2020 at 6:03 PM Praveen Kumar K S wrote: > Hello, > > I have a master and slave setup running and sometimes we have to do a > complete refresh on dev environments. How can I reset postgres server so > that it wipes out everything including WAL ? So that I configure > master/slave streaming replication again from scratch ? TIA. > > -- > > > *Regards,* > > > *K S Praveen Kumar* > -- *Regards,* *K S Praveen Kumar*
Re: [HELP] Reset postgres server
Hello, Yes, both are part of dev. Should I clean the data directory on both servers or only on master ? On Wed, Jun 10, 2020 at 7:50 PM Adrian Klaver wrote: > On 6/10/20 5:33 AM, Praveen Kumar K S wrote: > > Hello, > > > > I have a master and slave setup running and sometimes we have to do a > > complete refresh on dev environments. How can I reset postgres server so > > that it wipes out everything including WAL ? So that I configure > > master/slave streaming replication again from scratch ? TIA. > > Are both servers part of the dev setup? > > If so would deleting the content of $PGDATA and then doing an initdb on > it work? > To make above easier I would keep a copies of the *.conf files where you > could copy them back into $PGDATA after the initdb. > > > > > -- > > *Regards, > > > > * > > *K S Praveen Kumar > > > > * > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > -- *Regards,* *K S Praveen Kumar*
[HELP] General query - performance metrics
Hello, I have set up pgpool and there are 2 postgres servers behind it. I had run pgbench with and without pgpool and got the results. I would like to showcase the results for a presentation. Just thought of asking you people if there are any tools to generate nice results with charts and graphs ? TIA -- *Regards,* *K S Praveen Kumar*
[HELP] Query regarding logical replication slot
Hello, I'm running Postgres9.6 and configured master/slave with synchronous replication (synchronous_commit set to remote_apply) . In addition to that I have one logical replication slot on master. When master fails and one of the slaves is promoted to master, how can I have that logical replication slot and LSN on the slave ? -- *Regards,* *K S Praveen Kumar*
[HELP] Regarding how to install libraries
Hello, PostgreSQL libraries are required while compiling pgpool. I need to install postgresql-libs and postgresql-devel on Ubuntu Server 16.04 How can I install only these packages without having to install the entire postgres server ? TIA. -- *Regards,* *K S Praveen Kumar*
Re: [HELP] Regarding how to install libraries
Thank you. That solved the problem. On Fri, Jul 3, 2020 at 8:24 PM Adrian Klaver wrote: > On 7/3/20 7:21 AM, Adrian Klaver wrote: > > On 7/3/20 4:37 AM, Praveen Kumar K S wrote: > >> Hello, > >> > >> PostgreSQL libraries are required while compiling pgpool. I need to > >> install postgresql-libs and postgresql-devel on Ubuntu Server 16.04 > > > > Why not install pgpool from package? > > > > Assuming using PGDG repo: > > > > sudo apt install postgresql-12-pgpool2 > > This was assuming you have Postgres 12 installed. If necessary modify to > fit version actually installed. > > > > > > >> > >> How can I install only these packages without having to install the > >> entire postgres server ? TIA. > >> > >> -- > >> *Regards, > >> > >> * > >> *K S Praveen Kumar > >> > >> * > > > > > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > -- *Regards,* *K S Praveen KumarM: +91-9986855625 *
