New tablespace: just an advice

From: Moreno Andreo <moreno(dot)andreo(at)evolu-s(dot)it>
To: PostgreSQL mailing lists <pgsql-general(at)postgresql(dot)org>
Subject: New tablespace: just an advice
Date: 2018-10-15 12:48:07
Message-ID: cfb7a3f5-2cf6-59a5-2fd2-5b27b3b4bae1@evolu-s.it
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi everyone!

My space on my Debian 8 DB server is running a bit low (10% left of a
2TB disk), so, since it's not possible to have a primary MBR disk with
size > 2 TB, I decided to create another disk and map it on the server,
creating another tablespace on it and moving databases aross disks to
balance disk usage.

After creating a test server on this night's production server image, I
created a folder and assigned ownership to postgres user
mkdir /newdisk/tbsp_new
chown -R postgres /newdisk/tbsp_new

then created new tablespace
create tablspace tb2 location '/newdisk/tbsp_new';

and moved a database
alter database db set tablespace tb2;

As you can see a flat, basic tablespace with no customizations.
I just ran a VACUUM FULL on the cluster before creating tablespace.
After the ALTER DATABASE command ran successful, I checked disk space
(df -h) and saw some more free space on primary disk, and the same space
occupied on new disk. Just what I needed.
I psql'd in the cluster with the user's username connecting on the
database just migrated, and was able to SELECT and UPDATE (not tried
INSERTing).

Now, 2 questions.
1. Is it all or do I need to adjust something else about permissions,
indexes, vacuuming, etc...?
ALTERing the database namespace means copying its physical files to new
directory, but is it applied to all objects (indexes, triggers, etc)?

2. What will happen to who tries to access the database while it's being
moved from one tablespace to another?

Thanks in advance,
Moreno.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2018-10-15 13:18:29 Re: Enabling autovacuum per table
Previous Message Shrikant Bhende 2018-10-15 12:36:48 FATAL: terminating connection because protocol synchronization was lost