Re: database on selected disk

On Mon, 18 Oct 2021 at 22:35, Zahid Rahman <zahidr1000(at)gmail(dot)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(at)u1:~$ sudo systemctl stop postgresql(at)12-main(dot)service
postgres(at)u1:~$ mkdir /var/tmp/my_new_data_directory
postgres(at)u1:~$ rsync -av /var/lib/postgresql/12/main
/var/tmp/my_new_data_directory/
postgres(at)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(at)u1:~$ pg_conftool set data_directory
/var/tmp/my_new_data_directory/main
postgres(at)u1:~$ pg_conftool get data_directory
data_directory = '/var/tmp/my_new_data_directory/main'
postgres(at)u1:~$ sudo systemctl restart postgresql(at)12-main(dot)service
postgres(at)u1:~$ psql -c 'drop table t; create table t(id int);'

if i missed anything, this blog provides the commentary
How To Move a PostgreSQL Data Directory to a New Location on Ubuntu 16.04 |
DigitalOcean
<https://www.digitalocean.com/community/tutorials/how-to-move-a-postgresql-data-directory-to-a-new-location-on-ubuntu-16-04>

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(at)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(at)u1:~$ mkdir /tmp/mytablespace
postgres(at)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)