6

I am creating some databases using PostgreSQL but I want to save them on an external hard drive due to lack of memory in my computer.

How can I do this?

Filburt
  • 17,626
  • 12
  • 64
  • 115
Estefy
  • 424
  • 2
  • 6
  • 20
  • I need 4 times the storage capacity that my laptop can offer. Since I installed PostgreSQL in my computer, should I install it again in the external hard drive? – Estefy Mar 17 '16 at 20:10
  • 6
    There is no need for the database files to be on the same disk that PostgreSQL is installed on. –  Mar 17 '16 at 20:10

3 Answers3

9

You can store the database on another disk by specifying it as the data_directory setting. You need to specify this at startup and it will apply to all databases.

You can put it in postgresql.conf:

data_directory = '/volume/path/'

Or, specify it on the command line when you start PostgreSQL:

postgres -c data_directory='/volume/path/'

Reference: 18.2. File Locations

  • 1
    I'm trying "data_directory /Volumes/NameOfMyHardDrive" but I receive the following: data_directory: command not found – Estefy Mar 18 '16 at 21:21
  • 1
    You need to put it in `postgresql.conf` or on the command line. I've added these to my answer. –  Mar 20 '16 at 10:46
  • I have changed the data_directory but after that, postgres cluster status is down and psql can't connect to server! – Sihat Afnan Jun 23 '23 at 10:36
3

STEP 1: If postgresql is running, stop it:

sudo systemctl stop postgresql

STEP 2: Get the path to access your hard drive. (if Linux) Find and mount your hard drive by:

# Retrieve your device's name with:
sudo fdisk -l
# Then mount your device
sudo mount /dev/DEVICE_NAME YOUR_HD_DIR_PATH

STEP 3: Copy the existing database directory to the new location (in your hard drive) with rsync.

sudo rsync -av /var/lib/postgresql YOUR_HD_DIR_PATH

Then rename the previous postgres main dir with .bak extension to prevent conflicts

sudo mv /var/lib/postgresql/11/main /var/lib/postgresql/11/main.bak

Note: my postgres version was 11. Replace in path with your version.

STEP 4: Edit postgres configuration file:

sudo nano /etc/postgresql/11/main/postgresql.conf

Change the data_directory line with:

data_directory = 'YOUR_HD_DIR_PATH/postgresql/11/main'

STEP 5: Restart Postgres & Check everything is working

sudo systemctl start postgresql
pg_lsclusters

Output should shows status as 'online'

Ver Cluster Port Status Owner    Data directory                      Log file
11  main    5432 online postgres YOUR_HD_DIR_PATH/postgresql/11/main /var/log/postgresql/postgresql-11-main.log

Finally your can access your PostgresSQL with:

sudo -u postgres psql
Antiez
  • 679
  • 7
  • 11
  • I have changed the data_directory but after that, postgres cluster status is down and psql can't connect to server! If I undo my changes of data_dir, everything works fine.Where am I missing sth? – Sihat Afnan Jun 23 '23 at 10:37
0

You can try following the walkthrough here. This worked well for me and is similar to @Antiez's answer.

Currently, I am trying to do the same and the only conflict that I'm having at the moment is that it seems there is an issue with PostgreSQL's incremental backup and point-in-time recovery proccesses. I think it has something to do with folder permissions. If I try uploading a ~30MB csv to the postgres db, it will crash and the server will not start again because files cannot be written to the pg_wal directory. The only file in that directory is 000000010000000000000001 and does not move on to 000000010000000000000002 etc. while writing to a new table.

My stackoverflow post looking for a solution to this issue can be found here.

IamTrying
  • 39
  • 6