PostgreSQL is an open source relational database management system (RDBMS) with multiple versions and release series.
Here are some common PostgreSQL versions:
- PostgreSQL 9.x series: This is the older family of stable versions, including versions 9.0, 9.1, 9.2, 9.3, 9.4, 9.5, 9.6, etc.
- PostgreSQL 10.x series: This is the latest stable release series, including 10.0, 10.1, 10.2, 10.3, 10.4, etc.
- PostgreSQL 11.x series: This is the latest stable release series, including 11.0, 11.1, 11.2, 11.3, etc.
- PostgreSQL 12.x series: This is the latest stable release series, including 12.0, 12.1, 12.2, etc.
- PostgreSQL 13.x series: This is the latest stable release series, including 13.0, 13.1, 13.2, etc.
In addition, there are some development and beta versions of PostgreSQL, such as alpha, beta, etc., for testing and developing new features.
Install PostgreSQL 12.3 on CentOS 7 with one click and download it from a domestic mirror source:
#!/bin/bash
# 设置国内镜像源
echo "设置国内镜像源..."
echo "exclude=postgresql*" >> /etc/yum.repos.d/CentOS-Base.repo
echo "[pgdg12]" > /etc/yum.repos.d/pgdg.repo
echo "name=PostgreSQL 12 for RHEL/CentOS 7 - x86_64" >> /etc/yum.repos.d/pgdg.repo
echo "baseurl=https://mirrors.tuna.tsinghua.edu.cn/postgresql/repos/yum/12/redhat/rhel-7-x86_64" >> /etc/yum.repos.d/pgdg.repo
echo "enabled=1" >> /etc/yum.repos.d/pgdg.repo
echo "gpgcheck=0" >> /etc/yum.repos.d/pgdg.repo
# 安装 PostgreSQL 12.3
echo "安装 PostgreSQL 12.3..."
yum install -y postgresql12-server
# 初始化数据库
echo "初始化数据库..."
/usr/pgsql-12/bin/postgresql-12-setup initdb
# 启动 PostgreSQL
echo "启动 PostgreSQL 12.3..."
systemctl start postgresql-12
# 设置开机自启动
echo "设置开机自启动..."
systemctl enable postgresql-12
echo "PostgreSQL 12.3 安装完成！" Installation logs
[root@VM-4-9-centos ~]# chmod +x install_postgres.sh
[root@VM-4-9-centos ~]#
[root@VM-4-9-centos ~]#
[root@VM-4-9-centos ~]# ls /etc/yum.repos.d/
CentOS-Base.repo CentOS-Epel.repo docker-ce.repo docker.repo rancher-k3s-common.repo
[root@VM-4-9-centos ~]# sh install_postgres.sh
设置国内镜像源...
安装 PostgreSQL 12.3...
已加载插件：fastestmirror, langpacks
Determining fastest mirrors
docker | 3.5 kB 00:00:00
docker-ce-stable | 3.5 kB 00:00:00
epel | 4.7 kB 00:00:00
extras | 2.9 kB 00:00:00
os | 3.6 kB 00:00:00
pgdg12 | 3.6 kB 00:00:00
rancher-k3s-common-stable | 2.9 kB 00:00:00
updates | 2.9 kB 00:00:00
(1/9): epel/7/x86_64/group_gz | 99 kB 00:00:00
(2/9): epel/7/x86_64/updateinfo | 1.0 MB 00:00:00
(3/9): docker/primary_db | 111 kB 00:00:00
(4/9): pgdg12/group_gz | 245 B 00:00:00
(5/9): pgdg12/primary_db | 383 kB 00:00:00
(6/9): epel/7/x86_64/primary_db | 7.0 MB 00:00:00
(7/9): docker-ce-stable/7/x86_64/primary_db | 111 kB 00:00:00
(8/9): rancher-k3s-common-stable/primary_db | 4.4 kB 00:00:01
(9/9): updates/7/x86_64/primary_db | 22 MB 00:00:01
正在解决依赖关系
There are unfinished transactions remaining. You might consider running yum-complete-transaction, or "yum-complete-transaction --cleanup-only" and "yum history redo last", first to finish them. If those don't work you'll have to try removing/installing packages by hand (maybe package-cleanup can help).
--> 正在检查事务
---> 软件包 postgresql12-server.x86_64.0.12.15-1PGDG.rhel7 将被 安装
--> 正在处理依赖关系 postgresql12-libs(x86-64) = 12.15-1PGDG.rhel7，它被软件包 postgresql12-server-12.15-1PGDG.rhel7.x86_64 需要
--> 正在处理依赖关系 postgresql12(x86-64) = 12.15-1PGDG.rhel7，它被软件包 postgresql12-server-12.15-1PGDG.rhel7.x86_64 需要
--> 正在处理依赖关系 libpq.so.5()(64bit)，它被软件包 postgresql12-server-12.15-1PGDG.rhel7.x86_64 需要
--> 正在检查事务
---> 软件包 postgresql12.x86_64.0.12.15-1PGDG.rhel7 将被 安装
---> 软件包 postgresql12-libs.x86_64.0.12.15-1PGDG.rhel7 将被 安装
--> 解决依赖关系完成
依赖关系解决
================================================================================================================================================================================================
Package 架构 版本 源 大小
================================================================================================================================================================================================
正在安装:
postgresql12-server x86_64 12.15-1PGDG.rhel7 pgdg12 5.0 M
为依赖而安装:
postgresql12 x86_64 12.15-1PGDG.rhel7 pgdg12 1.6 M
postgresql12-libs x86_64 12.15-1PGDG.rhel7 pgdg12 377 k
事务概要
================================================================================================================================================================================================
安装 1 软件包 (+2 依赖软件包)
总下载量：7.0 M
安装大小：30 M
Downloading packages:
(1/3): postgresql12-libs-12.15-1PGDG.rhel7.x86_64.rpm | 377 kB 00:00:00
(2/3): postgresql12-12.15-1PGDG.rhel7.x86_64.rpm | 1.6 MB 00:00:00
(3/3): postgresql12-server-12.15-1PGDG.rhel7.x86_64.rpm | 5.0 MB 00:00:00
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
总计 10 MB/s | 7.0 MB 00:00:00
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
正在安装 : postgresql12-libs-12.15-1PGDG.rhel7.x86_64 1/3
正在安装 : postgresql12-12.15-1PGDG.rhel7.x86_64 2/3
正在安装 : postgresql12-server-12.15-1PGDG.rhel7.x86_64 3/3
验证中 : postgresql12-12.15-1PGDG.rhel7.x86_64 1/3
验证中 : postgresql12-server-12.15-1PGDG.rhel7.x86_64 2/3
验证中 : postgresql12-libs-12.15-1PGDG.rhel7.x86_64 3/3
已安装:
postgresql12-server.x86_64 0:12.15-1PGDG.rhel7
作为依赖被安装:
postgresql12.x86_64 0:12.15-1PGDG.rhel7 postgresql12-libs.x86_64 0:12.15-1PGDG.rhel7
完毕！
初始化数据库...
Initializing database ... OK
启动 PostgreSQL 12.3...
设置开机自启动...
Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql-12.service to /usr/lib/systemd/system/postgresql-12.service.
PostgreSQL 12.3 安装完成！
[root@VM-4-9-centos ~]# Set a password for PostgreSQL
- Open a terminal or command prompt window and log in to the PostgreSQL database server as superuser. You can log in using the following command:
- sudo -u postgres psql
- If prompted for a password, enter the password for your current operating system user.
- Once in the PostgreSQL database command-line prompt, use the following command to set a password for a specific database user. Replace with the <username> database user name for which you want to set a password:
- ALTER USER <username> WITH PASSWORD 'your_password';
- For example, to set the password for the user myuser to mypassword, you would execute the following command:
- ALTER USER myuser WITH PASSWORD 'mypassword';
- After you set the password, you can exit the PostgreSQL database command-line prompt with the following command:
- \q
Post-installation file location
The default installation location for PostgreSQL installed via the above script on CentOS 7 is as follows:
- Installation files: PostgreSQL binaries and executables are located in the /usr/pgsql-12/bin directory. The PostgreSQL library files are located in the /usr/pgsql-12/lib directory. The PostgreSQL plug-in file is located in the /usr/pgsql-12/share/extension directory.
- Configuration file: The main PostgreSQL configuration file, postgresql.conf, is located in the /var/lib/pgsql/12/data directory. pg_hba.conf file, which is used to configure client authentication rules, is also located in the /var/lib/pgsql/12/data directory.
Note that /var/lib/pgsql/12/data is the default data directory that contains the data files and configuration files for the database. You can change the location of the data directory by modifying the postgresql.conf file.
[root@VM-4-9-centos ~]# tree -L 2 /usr/pgsql-12/
/usr/pgsql-12/
├── bin
│ ├── clusterdb
│ ├── createdb
│ ├── createuser
│ ├── dropdb
│ ├── dropuser
│ ├── initdb
│ ├── pg_archivecleanup
│ ├── pg_basebackup
│ ├── pgbench
│ ├── pg_checksums
│ ├── pg_config
│ ├── pg_controldata
│ ├── pg_ctl
│ ├── pg_dump
│ ├── pg_dumpall
│ ├── pg_isready
│ ├── pg_receivewal
│ ├── pg_resetwal
│ ├── pg_restore
│ ├── pg_rewind
│ ├── pg_test_fsync
│ ├── pg_test_timing
│ ├── pg_upgrade
│ ├── pg_waldump
│ ├── postgres
│ ├── postgresql-12-check-db-dir
│ ├── postgresql-12-setup
│ ├── postmaster -> postgres
│ ├── psql
│ ├── reindexdb
│ └── vacuumdb
├── lib
│ ├── ascii_and_mic.so
│ ├── bitcode
│ ├── cyrillic_and_mic.so
│ ├── dict_int.so
│ ├── dict_snowball.so
│ ├── dict_xsyn.so
│ ├── euc2004_sjis2004.so
│ ├── euc_cn_and_mic.so
│ ├── euc_jp_and_sjis.so
│ ├── euc_kr_and_mic.so
│ ├── euc_tw_and_big5.so
│ ├── latin2_and_win1250.so
│ ├── latin_and_mic.so
│ ├── libecpg_compat.so.3 -> libecpg_compat.so.3.12
│ ├── libecpg_compat.so.3.12
│ ├── libecpg.so -> libecpg.so.6.12
│ ├── libecpg.so.6 -> libecpg.so.6.12
│ ├── libecpg.so.6.12
│ ├── libpgfeutils.a
│ ├── libpgtypes.so.3 -> libpgtypes.so.3.12
│ ├── libpgtypes.so.3.12
│ ├── libpq.so.5 -> libpq.so.5.12
│ ├── libpq.so.5.12
│ ├── libpqwalreceiver.so
│ ├── pgoutput.so
│ ├── plpgsql.so
│ ├── utf8_and_ascii.so
│ ├── utf8_and_big5.so
│ ├── utf8_and_cyrillic.so
│ ├── utf8_and_euc2004.so
│ ├── utf8_and_euc_cn.so
│ ├── utf8_and_euc_jp.so
│ ├── utf8_and_euc_kr.so
│ ├── utf8_and_euc_tw.so
│ ├── utf8_and_gb18030.so
│ ├── utf8_and_gbk.so
│ ├── utf8_and_iso8859_1.so
│ ├── utf8_and_iso8859.so
│ ├── utf8_and_johab.so
│ ├── utf8_and_sjis2004.so
│ ├── utf8_and_sjis.so
│ ├── utf8_and_uhc.so
│ └── utf8_and_win.so
└── share
├── errcodes.txt
├── extension
├── information_schema.sql
├── locale
├── man
├── pg_hba.conf.sample
├── pg_ident.conf.sample
├── pg_service.conf.sample
├── postgres.bki
├── postgres.description
├── postgresql-12-libs.conf
├── postgresql.conf.sample
├── postgres.shdescription
├── psqlrc.sample
├── snowball_create.sql
├── sql_features.txt
├── system_views.sql
├── timezonesets
└── tsearch_data The following is a summary of the directory structure:
- bin directory: Contains PostgreSQL 12 executables such as initdb, pg_dump, pg_restore, psql, etc.
- lib directory: Contains PostgreSQL 12 library files such as libecpg.so, libpgtypes.so, libpq.so, etc.
- share directory: Contains PostgreSQL 12 shared files, such as the extension directory (containing extensions), locale directory (containing locale files), man directory (containing man pages), pg_hba.conf.sample (authentication rule sample file), postgresql.conf.sample (configuration file example), etc.
When installing PostgreSQL, here is a detailed explanation of the functions and functions of some of the main files and directories:
- bin directory: initdb: Used to create a new PostgreSQL database cluster (data directory). pg_dump: Used to export the database as a SQL script or binary backup file. pg_restore: Used to restore a backup file created with pg_dump to the database. psql: PostgreSQL's command-line client tool for interacting with databases, executing queries, and managing databases. Other commands: For DB cluster management, backup and recovery, and so on.
- lib directory: libecpg.so, libpgtypes.so, libpq.so: PostgreSQL library files that provide an interface to interact with client applications. pgoutput.so: A plug-in for logical replication that allows changes to be passed from one PostgreSQL database to another. plpgsql.so: A plug-in to support the PL/pgSQL language, a procedural programming language in PostgreSQL. Additional library files: Provides support for other specific features and extensions.
- share directory: extension directory: Contains extensions to PostgreSQL that can be loaded to add additional functionality and features. locale directory: Contains locale files for internationalization and localization support, such as translation files and collations. man directory: Contains PostgreSQL man pages for viewing detailed documentation and help information about PostgreSQL. pg_hba.conf.sample: An example authentication profile that defines authentication rules when a client connects to a database. postgresql.conf.sample: An example configuration file that configures various parameters and settings for the database server.
[root@VM-4-9-centos ~]# ls /var/lib/pgsql/12/
backups data initdb.log
[root@VM-4-9-centos ~]#
[root@VM-4-9-centos ~]#
[root@VM-4-9-centos ~]# ls /var/lib/pgsql/12/data
base global pg_commit_ts pg_hba.conf pg_logical pg_notify pg_serial pg_stat pg_subtrans pg_twophase pg_wal postgresql.auto.conf postmaster.opts
current_logfiles log pg_dynshmem pg_ident.conf pg_multixact pg_replslot pg_snapshots pg_stat_tmp pg_tblspc PG_VERSION pg_xact postgresql.conf postmaster.pid - Base directory: This directory contains the tablespaces for the database. Each database has a subdirectory where the tables, indexes, and data files for that database are stored.
- Global Catalog: This directory stores global shared data, including system tablespaces, database templates, and other global objects.
- pg_commit_ts directory: This directory contains information related to transaction commit timestamps to support advanced features such as concurrency control and MVCC.
- pg_hba.conf: This is PostgreSQL's authentication profile that defines the authentication rules when a client connects to the database.
- pg_logical directory: If you have enabled logical replication, this directory contains related files for logical replication.
- pg_notify Directory: This directory is used to store information about the database notification functionality implemented through LISTEN and NOTIFY.
- pg_replslot directory: If you have enabled stream replication, this directory will contain the relevant files used to copy the slot.
- pg_serial Directory: If you use the SERIAL data type, the directory contains sequence-related files.
- pg_snapshots Directory: This directory is used to store snapshot information for concurrent transactions.
- pg_stat Directory: This directory contains statistics that track database server activity and performance.
- pg_stat_tmp Directory: This directory is used to store temporary statistics.
- pg_subtrans directory: This directory contains information related to child transactions to support concurrency control and MVCC.
- pg_tblspc directory: This directory contains additional tablespaces under which its files will be stored if you create additional tablespaces.
- pg_twophase directory: This directory contains information for two-phase commit transactions.
- pg_wal directory: This directory stores transaction log (WAL) files that are used to support the persistence and recovery capabilities of the database.
- postgresql.auto.conf: This is an auto-generated configuration file that records the settings and parameters of the automatic configuration.
- postgresql.conf: This is the main PostgreSQL configuration file for manually configuring various parameters and settings of the database server.
- postmaster.opts: This file holds the command-line options used when starting the PostgreSQL database server.
- PG_VERSION: This file contains the version number of the current PostgreSQL data directory.
- pg_xact directory: This directory contains transaction-related information to support concurrency control and MVCC.
- postmaster.pid: This file contains the process ID of the PostgreSQL database server.
These files and directories are an important part of a PostgreSQL database, storing the database's data, configuration, and metadata information, providing the necessary support for the operation and management of the database.
The configuration file postgresql.conf is detailed
Postgresql.conf is the main configuration file for the PostgreSQL database, which is used to configure various parameters and settings of the database server. Here is a detailed explanation of some common configuration options:
- listen_addresses: Specify the IP address or hostname of the PostgreSQL listener. By default, it is set to localhost, allowing only local connections. You can set it to * to allow connections from all IP addresses, or specify specific IP addresses.
- port: Specifies the port number on which the PostgreSQL database server listens. The default port is 5432. You can change the port as needed.
- max_connections: Specifies the maximum number of concurrent connections allowed by the database server. The default value is 100. Depending on your hardware resources and application needs, you can adjust this value.
- shared_buffers: Specifies the size of the shared memory buffer that the database server uses to cache data and indexes. This value is typically set to a fraction of system memory to improve performance. The default value is 128MB.
- work_mem: Specifies the amount of memory that each database session can use for temporary working memory such as sort and hash operations. The default value is 4MB. Depending on the needs of your query and workload, you can adjust this value appropriately.
- maintenance_work_mem: Specifies the maximum memory usage allowed when performing maintenance operations such as VACUUM, INDEX creation, and so on. The default value is 64MB. Depending on the size of your database and the complexity of your maintenance operations, you may need to adjust this value.
- effective_cache_size: Specifies the estimated system cache size for PostgreSQL. This value is used in query optimizer decisions to estimate the available disk cache. The default value is 4GB. Depending on the actual memory and disk cache capacity of your system, you can adjust this value.
- log_destination: Specifies the output destination for log messages. Can be set to stderr, csvlog, syslog, or eventlog. The default value is stderr, which outputs to the standard error stream.
- logging_collector: Specifies whether the log collector is enabled. If set to on, PostgreSQL uses a separate process to collect and record log messages. The default value is off.
- log_directory: Specify the directory where log files are stored. By default, log files are stored in the pg_log subdirectory in the PostgreSQL data directory.
- log_filename: Specifies the name template for the log file. By default, log files are named in the format postgresql-%Y-%m-%d_%H%M%S.log.
- log_rotation_age: Specify the interval for automatic rotation of log files. The default value is 1d, which means that the log file is rotated once a day.
- log_rotation_size: Specify the size threshold for automatic log file rotation. The default value is 10MB, which means that log files will be rotated when they reach this size.
- autovacuum: Specifies whether to enable automated VACUUM and ANALYZE processes. The default value is on, which enables the automated maintenance process.
- max_wal_size: Specifies the maximum size of the WAL (Write-Ahead Log) log file. The default value is 1GB. Depending on the write load and recovery needs of the database, you can adjust this value.
These are just some of the common options in the postgresql.conf configuration file, and there are many others that can be configured for your specific needs. Before modifying the configuration file, it is recommended that you understand the meaning and impact of each option and make appropriate adjustments based on your application and hardware environment.