Re: database on selected disk - Mailing list pgsql-admin
| From | Vijaykumar Jain |
|---|---|
| Subject | Re: database on selected disk |
| Date | |
| Msg-id | CAM+6J956-q6RfwC82JoAKcGvJ=o_CZ4GzvvYgnYG7GmopvEZig@mail.gmail.com Whole thread Raw |
| In response to | database on selected disk (Zahid Rahman <zahidr1000@gmail.com>) |
| List | pgsql-admin |
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)
pgsql-admin by date: