More than one Cluster on single server (single instance)

Hi,

Le lun. 18 juil. 2022 à 09:55, Daulat <daulat.dba@gmail.com> a écrit :

Hello Team,

We are planning to create multiple clusters on a single server (single
instance) with PostgreSQL V.10 to run multiple applications.

Planning to create a database cluster on a release 10 sounds already like a
bad idea, as release 10 will be maintained till november. After November
2022, no more bug fixes for this release. You really should use a more
recent release.

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?

It would make things easier for you to use a single cluster, and as many
databases as you want in this cluster.

How can we create multiple clusters under a single postgres version?

Mostly depends on your operating system, and on how you installed
PostgreSQL in the first place. The usual answer would be to run initdb for
each cluster.

How can we identify/allocate the resources to parameters like

shared_buffers, effective_cache_size, work_mem etc in case of multiple
clusters?

That's the hard part if you have many clusters. You will have to determine
by yourself how much memory each cluster will need. If you use only one
cluster (and many databases in it), you would use the usual formula to set
these parameters.

Regards.

--
Guillaume.

On 7/18/22 03:55, Daulat wrote:

Please share your thoughts on this approach?

How can we create multiple clusters under a single postgres version?

You can use Docker images.

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.

On 7/18/22 04:46, Guillaume Lelarge wrote:

Hi,

[snip]

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:

I have resolved this issue and it's working.
Thanks

On Thu, Jul 21, 2022 at 1:45 PM Daulat <daulat.dba@gmail.com> wrote: