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 *
