PostgreSQL directory structure and modify data directory

Source: Internet
Author: User

Tags: mobile base tar www mes through file storage storage. com

Initdb will specify a PGDATA directory, which is where PostgresQL stores data, such as:/var/lib/pgsql/10/data.
====== Display Data Catalog
1. Enter Psql
Sudo-u Postgres Psql
2. Locate the Data directory
postgres=# SHOW data_directory;
(1 row)

======pgdata the contents of the following items are presumably:
File storage Content
Version number of the Pg_version instance
POSTGRESQL.CONF Master configuration file (new feature, higher priority)
Pg_hba.conf Authentication Profile
pg_ident.conf Mapping configuration file
Postmaster.opts/usr/pgsql-10/bin/postgres "-D" "/var/lib/pgsql/10/data/"

Directory Name: Store content
Base: The default Tablespace directory in which the created table is stored. Each database will have a subdirectory under the base directory
Global: Some directories that share system tables. Postgres where your own meta database is stored (global DB)
PG_STAT_TMP: The storage directory for statistical information
PG_TBLSP: Stores links to the actual directory of individual user-created table spaces
Pg_twophase: Storage directory for distributed transactions when using the two-phase commit feature
= = The following directory features to be added ...

The following directory should be a version of 10.0
Directory of the Pg_clog:commit log
Pg_log: System log directory, you can view the log files in this directory when you query some system errors
pg_xlog:wal(write Ahead log write-ahead log) log directory

The base directory is the most important directory, and the data for each database is placed.
Every digital directory in the base directory for an OID of database, you can view the OID of each database by looking at the Pg_database table.
[[Email protected]]# ls/var/lib/pgsql/10/data/base
1 13805 13806
postgres=# Select OID, datname from Pg_database;
OID | Datname
13806 | Postgres
1 | Template1
13805 | Template0
(3 rows)

The data for each table (mostly) is placed in the base/(dboid)/(relfilenode) file:
postgres=# Select Relname, Relowner, Relfilenode from pg_class where relowner = 13805;
Relname | Relowner | Relfilenode
(0 rows)

Query the data file for the ' Item ' table. The ' item ' here is the table name.
lai=# Select Pg_relation_filepath (' item ');
(1 row)

======PG installation directory, PostgreSQL default in/usr/pgsql-10
Sub-directories are:
(1)/usr/pgsql-10/bin: Binary executable directory, this directory has postgres,psql and other executables.
(2)/usr/pgsql-10/lib: Dynamic Library Directory, PG program to run the required dynamic libraries are in this directory, such as
(3)/usr/pgsql-10/share: This directory contains the document and configuration template files, some expansion pack of SQL files, under the subdirectory extension of this directory.

Modify the owner of the Data_directory, directory permissions
Chown-r Postgres:postgres/home/pgdata
chmod 700/home/pgdata
====== Modifying the Data directory
1. Enter Psql
Sudo-u Postgres Psql
2. Locate the Data directory
SHOW data_directory;
3. Close the database
sudo systemctl stop PostgreSQL
sudo systemctl status PostgreSQL
4. We will copy the existing database directory to the new location with Rsync.
>> use the-a flag to retain permissions and other directory properties to avoid future escalation permissions issues.
>>-V provides verbose output in order to be able to follow the progress.
Note: Make sure there are no trailing slashes on the directory, and if you use tab completion you can join.
When there is a trailing slash, rsync copies the dump directory to the mount point instead of transferring it to a content PostgreSQL directory that contains:
sudo rsync-av/var/lib/postgresql/mnt/newdir
5. After the copy is complete, we will rename the current folder with a. bak extension and keep the file until we confirm that the move was successful. By renaming it, we will avoid the confusion that may arise from the files in the new and old locations:
sudo mv/var/lib/postgresql/10/data/var/lib/postgresql/10/data.bak
6. Modify the configuration file: postgresql.conf
data_directory = '/mnt/newdir/postgresql/10/data '
7. Start the database
sudo systemctl start PostgreSQL
sudo systemctl status PostgreSQL
8. Display Data Catalog
Sudo-u Postgres Psql
SHOW data_directory;
9. Deleting a backup
sudo rm-rf/var/lib/postgresql/9.5/main.bak
10. Restart
sudo systemctl restart PostgreSQL
sudo systemctl status PostgreSQL

Move the PostgreSQL Data directory to the new location

PG installation Directory
Postgres data Table File storage location

PostgreSQL directory structure and modify data directory

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

Tags Index: