Обсуждение: More than one Cluster on single server (single instance)
Hello Team,We are planning to create multiple clusters on a single server (single instance) with PostgreSQL V.10 to run multiple applications.
I don't know if it is a good idea to use a single machine to run n clusters but we are looking to minimize the cost of servers and other resources.Please share your thoughts on this approach?
How can we create multiple clusters under a single postgres version?
How can we identify/allocate the resources to parameters like shared_buffers, effective_cache_size, work_mem etc in case of multiple clusters?
You can use Docker images.Please share your thoughts on this approach?How can we create multiple clusters under a single postgres version?
How can we identify/allocate the resources to parameters like shared_buffers, effective_cache_size, work_mem etc in case of multiple clusters?Thanks
Each container can be adjusted to have its own memory and parameter file. Personally, I don't like that approach. I think that separating data into separate databases is enough separation even for the most fervent separatists (pun with the name of political movements is purely accidental).
-- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com
On 7/18/22 02:55, Daulat wrote: > > Hello Team, > > We are planning to create multiple clusters on a single server (single > instance) with PostgreSQL V.10 to run multiple applications. Version (9,6, 10, 111, 12...) does not matter. > I don't know if it is a good idea to use a single machine to run n > clusters but we are looking to minimize the cost of servers and other > resources. That depends on your circumstances. > Please share your thoughts on this approach? It allows you to do PITR backups without doing all-or-nothing restores. > How can we create multiple clusters under a single postgres version? Separate $PGDATA directories, and separate postgresql.conf files (with each config file specifying a different port number: 5432, 5433, 5434, etc) > > How can we identify/allocate the resources to parameters like > shared_buffers, effective_cache_size, work_mem etc in case of multiple > clusters? Very trickily. -- Angular momentum makes the world go 'round.
[snip]Hi,
It would make things easier for you to use a single cluster, and as many databases as you want in this cluster.
It would make PITR restoration of a single database (or small set of databases) impossible (since such backups -- and therefore restores -- are always binary "whole cluster").
Angular momentum makes the world go 'round.
We are thinking about this approach to consolidate some small applications of individual clients and to perform administration (data/log/pgbackrest backups etc.) separately which is not possible on a single cluster.
We have tried to create and start the cluster with different port 5433 on separate data directory but getting some errors.
- I replaced the default port with 5433 in the postgresql.conf file.
- We are not using Ubuntu , hence, I think we can’t use the pg_cluster
./initdb -D /opt/PostgreSQL-10/whr_data1_5433 -U postgres -W
./pg_ctl -D /opt/PostgreSQL-10/whr_data1_5433 -l /opt/PostgreSQL-10/whr_dlog/start.log start
Error while creating Cluster:
[postgres@ip-172-31-32-99 bin]$ cat /opt/PostgreSQL-10/whr_dlog/start.log
2022-07-18 13:11:14.404 UTC [13418] LOG: could not bind IPv4 address "127.0.0.1": Address already in use
2022-07-18 13:11:14.404 UTC [13418] HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
2022-07-18 13:11:14.404 UTC [13418] WARNING: could not create listen socket for "localhost"
2022-07-18 13:11:14.404 UTC [13418] FATAL: could not create any TCP/IP sockets
2022-07-18 13:11:14.404 UTC [13418] LOG: database system is shut down
[postgres@ip-172-31-32-99 bin]$
On 7/18/22 04:46, Guillaume Lelarge wrote:[snip]Hi,It would make things easier for you to use a single cluster, and as many databases as you want in this cluster.
It would make PITR restoration of a single database (or small set of databases) impossible (since such backups -- and therefore restores -- are always binary "whole cluster").--
Angular momentum makes the world go 'round.
Thanks for your inputs.We are thinking about this approach to consolidate some small applications of individual clients and to perform administration (data/log/pgbackrest backups etc.) separately which is not possible on a single cluster.
We have tried to create and start the cluster with different port 5433 on separate data directory but getting some errors.
- I replaced the default port with 5433 in the postgresql.conf file.
- We are not using Ubuntu , hence, I think we can’t use the pg_cluster
./initdb -D /opt/PostgreSQL-10/whr_data1_5433 -U postgres -W
./pg_ctl -D /opt/PostgreSQL-10/whr_data1_5433 -l /opt/PostgreSQL-10/whr_dlog/start.log start
Error while creating Cluster:
[postgres@ip-172-31-32-99 bin]$ cat /opt/PostgreSQL-10/whr_dlog/start.log
2022-07-18 13:11:14.404 UTC [13418] LOG: could not bind IPv4 address "127.0.0.1": Address already in use
2022-07-18 13:11:14.404 UTC [13418] HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
2022-07-18 13:11:14.404 UTC [13418] WARNING: could not create listen socket for "localhost"
2022-07-18 13:11:14.404 UTC [13418] FATAL: could not create any TCP/IP sockets
2022-07-18 13:11:14.404 UTC [13418] LOG: database system is shut down
[postgres@ip-172-31-32-99 bin]$
On Mon, Jul 18, 2022 at 7:12 PM Ron <ronljohnsonjr@gmail.com> wrote:On 7/18/22 04:46, Guillaume Lelarge wrote:[snip]Hi,It would make things easier for you to use a single cluster, and as many databases as you want in this cluster.
It would make PITR restoration of a single database (or small set of databases) impossible (since such backups -- and therefore restores -- are always binary "whole cluster").--
Angular momentum makes the world go 'round.