PostgreSQL version : 11.2
OS : RHEL or Oracle Linux 7.6 (Yet to be decided)
I am at the design stage of setting up a production database. In production, the DB will be around 300GB to 400GB in size.
This is what I have in mind. Please let me know if this is a good idea for a PostgreSQL production deployment.
I will have the following file system mounted with the following sizes:
/db ----> 50 GB
/pgdata ---> 500 GB
I will initialize the database cluster in the custom location /db/postgres/pg11/data
.
And right from the start I will start creating tablespaces like this:
CREATE TABLESPACE orders_tbs LOCATION '/pgdata/<db_name>/orders_tbs';
...and place business objects in these tablespaces like this:
CREATE TABLE orders (id int, order_item text) tablespace orders_tbs;
Thank You Arkhena, CL
In the above case, I thought of creating a separate filesystem for datafiles (/pgdata
) and keeping the config files and logs in /db
.
So, my idea was bad.
Since I am in RHEL/Oracle Linux , by default, my $PGDATA
will be /var/lib/pgsql/11/data
.
But, I prefer to have my $PGDATA
in a custom location like /db/postgres/pg11/data
Since the datafiles reside in $PGDATA/base
directory , how about creating a disk layout like below using LVM?
A 50GB filesystem for $PGDATA's top parent directory /db
and a separate 500 GB filesystem for $PGDATA/base
directory ?
[root@localhost ~]# df -Ph
Filesystem Size Used Avail Use% Mounted on
<output snipped>
.
.
/dev/mapper/VolGroup1-LogVol02 50G 23M 49.9G 1% /db
/dev/mapper/VolGroup1-LogVol04 500G 2.7M 499.9G 1% /db/postgres/pg11/data/base
I need to check with our Linux Admin on how the above disk layout can be optimally created without causing any storage bottlenecks.