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

From DISI
Jump to navigation Jump to search
Line 57: Line 57:
  
 
Trouble shooting, you may encounter the following error message:
 
Trouble shooting, you may encounter the following error message:
 +
 
● postgresql-10.service - PostgreSQL 10 database server
 
● postgresql-10.service - PostgreSQL 10 database server
 
   Loaded: loaded (/usr/lib/systemd/system/postgresql-10.service; enabled; vendor preset: disabled)
 
   Loaded: loaded (/usr/lib/systemd/system/postgresql-10.service; enabled; vendor preset: disabled)
Line 74: Line 75:
 
Jul 24 13:04:11 yod.cluster.ucsf.bkslab.og systemd[1]: postgresql-10.service failed.
 
Jul 24 13:04:11 yod.cluster.ucsf.bkslab.og systemd[1]: postgresql-10.service failed.
 
Hint: Some lines were ellipsized, use -l to show in full.
 
Hint: Some lines were ellipsized, use -l to show in full.
 +
 +
Job for postgresql-10.service failed because the control process exited with error code. See "systemctl status postgresql-10.service" and "journalctl -xe" for details.
 +
 +
journalctl -xe              # check the error message
 +
There is a ******.lock file block access to the PostgreSQL service. Change the ownership or access roles of this file to solve this problem.
 +
 
</pre>
 
</pre>

Revision as of 21:01, 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
...

(6) Restart PostgreSQL service

sudo systemctl start postgresql-10

Trouble shooting, you may encounter the following error message:

● postgresql-10.service - PostgreSQL 10 database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql-10.service; enabled; vendor preset: disabled)
   Active: failed (Result: exit-code) since Tue 2018-07-24 13:04:11 PDT; 38s ago
     Docs: https://www.postgresql.org/docs/10/static/
  Process: 23952 ExecStart=/usr/pgsql-10/bin/postmaster -D ${PGDATA} (code=exited, status=1/FAILURE)
  Process: 23946 ExecStartPre=/usr/pgsql-10/bin/postgresql-10-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
 Main PID: 23952 (code=exited, status=1/FAILURE)

Jul 24 13:04:11 yod.cluster.ucsf.bkslab.og systemd[1]: Starting PostgreSQL 10 database server...
Jul 24 13:04:11 yod.cluster.ucsf.bkslab.og postmaster[23952]: 2018-07-24 13:04:11.944 PDT [23952] LOG:  cou...se
Jul 24 13:04:11 yod.cluster.ucsf.bkslab.og postmaster[23952]: 2018-07-24 13:04:11.944 PDT [23952] HINT:  Is...y.
Jul 24 13:04:11 yod.cluster.ucsf.bkslab.og postmaster[23952]: 2018-07-24 13:04:11.944 PDT [23952] LOG:  cou...se
Jul 24 13:04:11 yod.cluster.ucsf.bkslab.og systemd[1]: postgresql-10.service: main process exited, code=ex...URE
Jul 24 13:04:11 yod.cluster.ucsf.bkslab.og systemd[1]: Failed to start PostgreSQL 10 database server.
Jul 24 13:04:11 yod.cluster.ucsf.bkslab.og systemd[1]: Unit postgresql-10.service entered failed state.
Jul 24 13:04:11 yod.cluster.ucsf.bkslab.og systemd[1]: postgresql-10.service failed.
Hint: Some lines were ellipsized, use -l to show in full.

Job for postgresql-10.service failed because the control process exited with error code. See "systemctl status postgresql-10.service" and "journalctl -xe" for details.

journalctl -xe               # check the error message
There is a ******.lock file block access to the PostgreSQL service. Change the ownership or access roles of this file to solve this problem.