PostgreSQL directory structure and modify data directory

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
Data_directory
------------------------
/var/lib/pgsql/10/data
(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
postgresql.auto.conf (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/"
Postmaster.pid
...


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 ...
Log
Pg_commit_ts
Pg_dynshmem
Pg_logical
Pg_multixact
Pg_notify
Pg_replslot
Pg_serial
Pg_snapshots
Pg_stat
Pg_subtrans
Pg_wal
Pg_xact









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 ');
Pg_relation_filepath
----------------------
base/16385/24595
(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 libpg.so.
(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
https://www.howtoing.com/how-to-move-a-postgresql-data-directory-to-a-new-location-on-ubuntu-16-04





PG installation Directory
79563881
Postgres data Table File storage location
60962451
http://veryr.com/posts/postgres-storage-location/











PostgreSQL directory structure and modify data directory