PostgreSQL directory structure and modify data directory

Source: Internet
Author: User
Tags postgresql psql rsync

Initdb will specify a PGDATA directory, which is where postgresql stores data, such as:/var/lib/pgsql/10/data.

On update cascade postgresql
====== Display Data Catalog
1. Enter Psql,postgresql update
Sudo-u Postgres Psql
2. Locate the Data directory
postgres=# SHOW data_directory;

Python postgresql update
(1 row)

Postgresql update

======pgdata the contents of the following items are presumably:
File storage Content,postgresql vs mysql
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/"

Postgresql update from select

Directory Name: Store content and install postgresql ubuntu
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,update postgresql install postgresql

Postgresql update join
PG_TBLSP: Stores links to the actual directory of individual user-created table spaces,postgresql download
Pg_twophase: Storage directory for distributed transactions when using the two-phase commit feature
= = The following directory features to be added ...

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.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.