Thread: database on selected disk
Hi I have two disks attached to my Raspberry pi 4 . One disk is a 2TB with a partition called postgres on /dev/sdb/sd1 . When creating databases this is where I wish to create them. This is not the disk with the operating system installed . If I install postgres with pgadmin using apt install, any database I create will be installed on disk /dev/sda/sda1 by default. Can you please give me a HOW-TO on choosing the partition on a particular disk when creating a database or perhaps even a table. Thanks Zahid https://www.backbutton.org ¯\_(ツ)_/¯ ♡۶♡ ۶♡۶
On Mon, 18 Oct 2021 at 22:35, Zahid Rahman <zahidr1000@gmail.com> wrote:
Hi
I have two disks attached to my Raspberry pi 4 .
One disk is a 2TB with a partition called postgres on /dev/sdb/sd1 .
When creating databases this is where I wish to create them.
This is not the disk with the operating system installed .
If I install postgres with pgadmin using apt install,
any database I create will be installed on disk /dev/sda/sda1 by default.
Can you please give me a HOW-TO on choosing the partition on a
particular disk when creating a database or perhaps even a table.
you can stop postgresql, move over the contents of your data_directory to the location you want it to run from,
update data_directory param in postgresql.conf and restart postgresql.
postgres@u1:~$ sudo systemctl stop postgresql@12-main.service
postgres@u1:~$ mkdir /var/tmp/my_new_data_directory
postgres@u1:~$ rsync -av /var/lib/postgresql/12/main /var/tmp/my_new_data_directory/
postgres@u1:~$ ls -ld /var/tmp/my_new_data_directory/main
drwx------ 19 postgres postgres 4096 Oct 18 22:46 /var/tmp/my_new_data_directory/main
postgres@u1:~$ pg_conftool set data_directory /var/tmp/my_new_data_directory/main
postgres@u1:~$ pg_conftool get data_directory
data_directory = '/var/tmp/my_new_data_directory/main'
postgres@u1:~$ sudo systemctl restart postgresql@12-main.service
postgres@u1:~$ psql -c 'drop table t; create table t(id int);'
if i missed anything, this blog provides the commentary
also, there are other options, of having the new disk as an optional tablespace. and then creating tables on the new tablespace.
but that requires explicit mention.
postgres@u1:~$ psql
psql (12.8 (Ubuntu 12.8-0ubuntu0.20.04.1))
Type "help" for help.
postgres=# show data_directory;
data_directory
-------------------------------------
/var/tmp/my_new_data_directory/main
(1 row)
postgres=# \q
postgres@u1:~$ mkdir /tmp/mytablespace
postgres@u1:~$ psql
psql (12.8 (Ubuntu 12.8-0ubuntu0.20.04.1))
Type "help" for help.
postgres=# create tablespace mytablespace location '/tmp/mytablespace'; -- do not use /tmp, this is for demo only
CREATE TABLESPACE
postgres=# create database foo tablespace mytablespace;
CREATE DATABASE
postgres=# create table t1(id int) tablespace mytablespace;
CREATE TABLE
postgres=# \db+
List of tablespaces
Name | Owner | Location | Access privileges | Options | Size | Description
--------------+----------+--------------------------+-------------------+---------+---------+-------------
mytablespace | postgres | /tmp/mytablespace | | | 7969 kB |
pg_default | postgres | | | | 24 MB |
pg_global | postgres | | | | 623 kB |
(4 rows)
Am 18.10.21 um 19:04 schrieb Zahid Rahman: > Hi > > I have two disks attached to my Raspberry pi 4 . > > One disk is a 2TB with a partition called postgres on /dev/sdb/sd1 . > When creating databases this is where I wish to create them. > > This is not the disk with the operating system installed . > If I install postgres with pgadmin using apt install, > any database I create will be installed on disk /dev/sda/sda1 by default. > > Can you please give me a HOW-TO on choosing the partition on a > particular disk when creating a database or perhaps even a table. > > Thanks > Zahid > > Hi Zahid, when setting up a database cluster with initdb, you determine the directory path for the cluster. So make sure the directory path resides on the disk you want it to be. What is also possible is using a symbolic link to redirect to another directory on the intended disk. On the other hand, it's not possible to have some databases of a cluster on one drive and some on another. A database cluster cannot be distributed among disks. Since you can have any number of database clusters on a machine, this doesn't impose a problem. Regards, Holger -- Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
Attachment
On 19/10/2021 06:51, Holger Jakobs wrote: > > On the other hand, it's not possible to have some databases of a > cluster on one drive and some on another. A database cluster cannot be > distributed among disks. Except by using Tablespaces - https://www.postgresql.org/docs/14/manage-ag-tablespaces.html -- Paul Paul Smith Computer Services support@pscs.co.uk - 01484 855800 -- Paul Smith Computer Services Tel: 01484 855800 Vat No: GB 685 6987 53 Sign up for news & updates at http://www.pscs.co.uk/go/subscribe
Thanks.
I had a bit of luck with searching around.
I think if I create a TABLESPACE and point that to any disk partition or mount point
CREATE TABLESPACE tablespace_name OWNER user_name LOCATION directory_path;
then when creating a database or even a table I just need to include the TABLESPACE name .
CREATE DATABASE logistics TABLESPACE ts_primary;
This will allow me to use the secondary disk for purely databases.
Thanks
On Tue, 19 Oct 2021, 06:51 Holger Jakobs, <holger@jakobs.com> wrote:
Am 18.10.21 um 19:04 schrieb Zahid Rahman:
> Hi
>
> I have two disks attached to my Raspberry pi 4 .
>
> One disk is a 2TB with a partition called postgres on /dev/sdb/sd1 .
> When creating databases this is where I wish to create them.
>
> This is not the disk with the operating system installed .
> If I install postgres with pgadmin using apt install,
> any database I create will be installed on disk /dev/sda/sda1 by default.
>
> Can you please give me a HOW-TO on choosing the partition on a
> particular disk when creating a database or perhaps even a table.
>
> Thanks
> Zahid
>
>
Hi Zahid,
when setting up a database cluster with initdb, you determine the
directory path for the cluster.
So make sure the directory path resides on the disk you want it to be.
What is also possible is using a symbolic link to redirect to another
directory on the intended disk.
On the other hand, it's not possible to have some databases of a cluster
on one drive and some on another. A database cluster cannot be
distributed among disks.
Since you can have any number of database clusters on a machine, this
doesn't impose a problem.
Regards,
Holger
--
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012