pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feeddatabase on selected disk
5+ messages / 4 participants
[nested] [flat]
* database on selected disk
@ 2021-10-18 17:04 Zahid Rahman <zahidr1000@gmail.com>
0 siblings, 2 replies; 5+ messages in thread
From: Zahid Rahman @ 2021-10-18 17:04 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
¯\_(ツ)_/¯
♡۶♡ ۶♡۶
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: database on selected disk
@ 2021-10-18 17:25 Vijaykumar Jain <vijaykumarjain.github@gmail.com>
parent: Zahid Rahman <zahidr1000@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: Vijaykumar Jain @ 2021-10-18 17:25 UTC (permalink / raw)
To: Zahid Rahman <zahidr1000@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.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
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-lo...;
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)
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: database on selected disk
@ 2021-10-19 05:51 Holger Jakobs <holger@jakobs.com>
parent: Zahid Rahman <zahidr1000@gmail.com>
1 sibling, 2 replies; 5+ messages in thread
From: Holger Jakobs @ 2021-10-19 05:51 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
Attachments:
OpenPGP_signature (application/pgp-signature, 202B) download
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: database on selected disk
@ 2021-10-19 08:26 Paul Smith <paul@pscs.co.uk>
parent: Holger Jakobs <holger@jakobs.com>
1 sibling, 0 replies; 5+ messages in thread
From: Paul Smith @ 2021-10-19 08:26 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: database on selected disk
@ 2021-10-19 11:59 Zahid Rahman <zahidr1000@gmail.com>
parent: Holger Jakobs <holger@jakobs.com>
1 sibling, 0 replies; 5+ messages in thread
From: Zahid Rahman @ 2021-10-19 11:59 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2021-10-19 11:59 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-10-18 17:04 database on selected disk Zahid Rahman <zahidr1000@gmail.com>
2021-10-18 17:25 ` Vijaykumar Jain <vijaykumarjain.github@gmail.com>
2021-10-19 05:51 ` Holger Jakobs <holger@jakobs.com>
2021-10-19 08:26 ` Paul Smith <paul@pscs.co.uk>
2021-10-19 11:59 ` Zahid Rahman <zahidr1000@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox