How to do indexing, partition, and migration in Postgres 10: Difference between revisions
Jump to navigation
Jump to search
No edit summary |
No edit summary |
||
Line 80: | Line 80: | ||
journalctl -xe # check the error message | 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. | 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> | |||
(7) Restart psql and check data directory again. | |||
<pre> | |||
postgres=# SHOW data_directory; | |||
Output | |||
data_directory | |||
------------------------- | |||
/ssd/disk1/psql_10_data | |||
(1 row) | |||
</pre> | </pre> |
Revision as of 21:03, 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.
(7) Restart psql and check data directory again.
postgres=# SHOW data_directory; Output data_directory ------------------------- /ssd/disk1/psql_10_data (1 row)