From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Tony Sullivan <tony(at)exquisiteimages(dot)com>, pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Multiple PostgreSQL instances on one machine |
Date: | 2018-06-08 20:59:32 |
Message-ID: | 7e691ce0-3e30-731d-1780-4bc5fb25960d@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Chapman Flack | 2018-06-08 21:06:43 | Re: Performance problem postgresql 9.5 |
Previous Message | Vick Khera | 2018-06-08 20:38:28 | Re: Multiple PostgreSQL instances on one machine |