Обсуждение: Multiple PostgreSQL instances on one machine
I am trying to consolidate some machines in my server room particularly in the testing environment and I was hoping someone could point me in the right direction. I currently have three machines running PostgreSQL for testing purposes. Each week a backup is made of the production database and this is deployed onto these three machines. None of these machines is any where near capacity. I would like to host all three instances on one machine. I know that "initdb" can be used to create additional installations, but the part I am unsure about is the tablespace. The production database has a tablespace defined that resides on its SSD. When I use the production backup, I have to create a mount point on the test machine with the same name as the one on the production database. I am not certain how I would handle this situation if I am serving three separate instances of that database from one computer.
If they are just test environments, why a whole dedicated cluster per instance? Just give each a unique name for the database and run it all on one cluster.
I'd also go back and reconsider why these are separate machines in the first place and make sure you're not violating any assumptions that were made.
On Fri, Jun 8, 2018 at 4:29 PM, Tony Sullivan <tony@exquisiteimages.com> wrote:
I am trying to consolidate some machines in my server room particularly in
the testing environment and I was hoping someone could point me in the
right direction.
I currently have three machines running PostgreSQL for testing purposes.
Each week a backup is made of the production database and this is deployed
onto these three machines. None of these machines is any where near
capacity. I would like to host all three instances on one machine.
I know that "initdb" can be used to create additional installations, but
the part I am unsure about is the tablespace. The production database has
a tablespace defined that resides on its SSD. When I use the production
backup, I have to create a mount point on the test machine with the same
name as the one on the production database. I am not certain how I would
handle this situation if I am serving three separate instances of that
database from one computer.
On 06/08/2018 01:29 PM, Tony Sullivan wrote: > I am trying to consolidate some machines in my server room particularly in > the testing environment and I was hoping someone could point me in the > right direction. > > I currently have three machines running PostgreSQL for testing purposes. > Each week a backup is made of the production database and this is deployed > onto these three machines. None of these machines is any where near > capacity. I would like to host all three instances on one machine. > > I know that "initdb" can be used to create additional installations, but > the part I am unsure about is the tablespace. The production database has > a tablespace defined that resides on its SSD. When I use the production > backup, I have to create a mount point on the test machine with the same > name as the one on the production database. I am not certain how I would > handle this situation if I am serving three separate instances of that > database from one computer. > From here: https://www.postgresql.org/docs/10/static/manage-ag-tablespaces.html "The directory $PGDATA/pg_tblspc contains symbolic links that point to each of the non-built-in tablespaces defined in the cluster. Although not recommended, it is possible to adjust the tablespace layout by hand by redefining these links. Under no circumstances perform this operation while the server is running. Note that in PostgreSQL 9.1 and earlier you will also need to update the pg_tablespace catalog with the new locations. (If you do not, pg_dump will continue to output the old tablespace locations.)" A quick test here showed that you can create a new directory and move the contents of the existing tablespace into it and then relink the tablespace to the new location. create tablespace tblspc_test location '/home/postgres/test_tblspc'; create table tblspc_table(id int) tablespace tblspc_test ; select * from pg_class where relname = 'tblspc_table'; -[ RECORD 1 ]-------+------------- relname | tblspc_table relnamespace | 2200 reltype | 1836557 reloftype | 0 relowner | 10 relam | 0 relfilenode | 1836555 reltablespace | 1836554 So in $DATA/pg_tblspc: 1836554 -> /home/postgres/test_tblspc/ mkdir /home/postgres/tblspc_1 cd /home/postgres/test_tblspc/ cp -r PG_10_201707211/ ../tblspc_1/ shutdown Postgres cd $DATA/pg_tblspc rm 1836554 ln -s /home/postgres/tblspc_1 1836554 start Postgres select * from pg_class where relname = 'tblspc_table'; -[ RECORD 1 ]-------+------------- relname | tblspc_table relnamespace | 2200 reltype | 1836557 reloftype | 0 relowner | 10 relam | 0 relfilenode | 1836555 reltablespace | 1836554 -- Adrian Klaver adrian.klaver@aklaver.com
On Fri, Jun 8, 2018 at 3:29 PM Tony Sullivan <tony@exquisiteimages.com> wrote: > > I am trying to consolidate some machines in my server room particularly in > the testing environment and I was hoping someone could point me in the > right direction. > > I currently have three machines running PostgreSQL for testing purposes. > Each week a backup is made of the production database and this is deployed > onto these three machines. None of these machines is any where near > capacity. I would like to host all three instances on one machine. > > I know that "initdb" can be used to create additional installations, but > the part I am unsure about is the tablespace. The production database has > a tablespace defined that resides on its SSD. When I use the production > backup, I have to create a mount point on the test machine with the same > name as the one on the production database. I am not certain how I would > handle this situation if I am serving three separate instances of that > database from one computer. Generally speaking, this not an issue. You can consolidate all three machines to one cluster, or to multiple clusters running on same box. The approaches have pros and cons relative to each other. I would personally tend to simple create different databases on the same cluster unless you had a reason not to. The database would share: *) database roles (in particular superuser accounts) *) WAL (possibly presenting a i/o bottleneck) *) replication for HS/SR (which is all or nothing at the cluster level) *) shared memory *) availability -- if one database crashes they will all crash *) tcp/ip port (this is good -- you don't have to worry about what port you're connecting to) merlin