Difference between revisions of "How to do indexing, partition, and migration in Postgres 10"

From DISI
Jump to navigation Jump to search
Line 30: Line 30:
  
 
(3) Create new folder in destination directory
 
(3) Create new folder in destination directory
<pre>
+
<pre>
 
cd /ssd/disk1             
 
cd /ssd/disk1             
 
mkdir psql_10_data                                      # create a new folder
 
mkdir psql_10_data                                      # create a new folder
 
chown postgres psql_10_data                    # Postgres requires exclusive ownership and access to the data directory.  
 
chown postgres psql_10_data                    # Postgres requires exclusive ownership and access to the data directory.  
 
chmod 700 psql_10_data                            # Change read, write, and execute authority of this folder. No group or world access to this folder. Required by Postgres.
 
chmod 700 psql_10_data                            # Change read, write, and execute authority of this folder. No group or world access to this folder. Required by Postgres.
 +
</pre>
 +
 +
(4) Copy current data and configure files to new folder
 +
<pre>
 +
cp -R /var/lib/pgsql/10/data/* /ssd/disk1/psql_10_data/
 +
</pre>
 +
 +
(5) Point to the new data location
 +
<pre>
 +
vim /var/lib/pgsql/10/data/postgresql.conf      # Edit the configuration file in old folder.
 +
 +
...
 +
data_directory = '/ssd/disk1/psql_10_data'              # use data in another directory
 +
...
 +
 
</pre>
 
</pre>

Revision as of 20:47, 3 August 2018

This tutorial shows how to do data migration, partition, and indexing in Postgres 10.

1. Migrate data directory

The dataset is quite large, and it will take up about 220GB of hard disk once loaded and indexed into Postgres. It is better to move the database storage to another large disk instead of the default (root) one. A Solid State Disk (SSD) is preferred for faster disk access.

(1) Check current Postgres data directory.

sudo -i 
su - postgres
psql
SHOW data_directory;
Output:

     data_directory      
-------------------------
 /var/lib/pgsql/10/data
(1 row)

(2) Quit all Postgres Processes and terminate Postgres service.

\q     # quit from psql
sudo systemctl stop postgresql-10            # stop postgres
sudo systemctl status postgresql-10         # check its status

(3) Create new folder in destination directory

cd /ssd/disk1             
mkdir psql_10_data                                       # create a new folder
chown postgres psql_10_data                     # Postgres requires exclusive ownership and access to the data directory. 
chmod 700 psql_10_data                             # Change read, write, and execute authority of this folder. No group or world access to this folder. Required by Postgres.

(4) Copy current data and configure files to new folder

cp -R /var/lib/pgsql/10/data/* /ssd/disk1/psql_10_data/

(5) Point to the new data location

vim /var/lib/pgsql/10/data/postgresql.conf       # Edit the configuration file in old folder.

...
data_directory = '/ssd/disk1/psql_10_data'              # use data in another directory
...