tlinux 3.1 installing PostgreSQL 13 x
TencentOS Server is a customized server operating system independently developed by Tencent. The system integrates many services
The advantages of the device series include the self-developed software, which is convenient for users to operate and use, and provides all-round (kernel and user state)
Operating system support.
System features: safe, easy to use, stable, fast and long-term support. The installation image provides various software support commonly used by the server. At the same time, you can use the online software source to install and update the software. This instruction is applicable to the installation and use of Tencent OS Server 2.4 release and 3.1 release. This article takes Tencent OS Server 3.1 environment as an example to explain PostgreSQL 13 Installation of version X.
$1 | prerequisites
In order to install the package, you need to log in to the system as root or other user with sudo permission.
$2 | installation steps
- 2.1 list available PostgreSQL modules
dnf module list postgresql
The output shows the results. postgresql has four versions. Each version has two parts: server and client. Server version 10 is the default version:
[root@TENCENT64 ~]# dnf module list postgresql Last metadata expiration check: 0:01:22 ago on Fri 28 Jan 2022 05:14:05 PM CST. TencentOS Server 3.1 - AppStream Name Stream Profiles Summary postgresql 9.6 client, server [d] PostgreSQL server and client module postgresql 10 [d] client, server [d] PostgreSQL server and client module postgresql 12 client, server [d] PostgreSQL server and client module TencentOS Server 3.1 - TencentOS-AppStream Name Stream Profiles Summary postgresql 9.6 client, server [d] PostgreSQL server and client module postgresql 10 [d] client, server [d] PostgreSQL server and client module postgresql 12 client, server [d] PostgreSQL server and client module postgresql 13 [e] client, server [d] [i] PostgreSQL server and client module Hint: [d]efault, [e]nabled, [x]disabled, [i]nstalled
- 2.2 install server version 13, enter:
sudo dnf install @postgresql:13
- 2.3 you may also want to install the contrib package, which provides some additional features to the PostgreSQL database:
sudo dnf install postgresql-contrib
- 2.4 once the installation is completed, use the following command to initialize the PostgreSQL database:
sudo postgresql-setup initdb ... Initializing database ... OK
- 2.5 start the PostgreSQL service and enable the PostgreSQL service.
sudo systemctl enable --now postgresql
- 2.6 verify the installation results, use psql tool to connect to PostgreSQL database server and print its version number.
sudo -u postgres psql -c "SELECT version();"
Output display results:
[root@TENCENT64 ~]# sudo -u postgres psql -c "SELECT version();" could not change directory to "/root": Permission denied version ------------------------------------------------------------------------------------------------------------ PostgreSQL 13.3 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 8.4.1 20200928 (Red Hat 8.4.1-1), 64-bit (1 row)
$3 | PostgreSQL role and identity authentication method
PostgreSQL supports multiple authentication methods. The most commonly used methods are as follows: * * trust, password, ident, peer**
Auth method: Specifies the authentication method to use when a connection matches this record.
For more details, please see = > https://www.w3cschool.cn/postgresql13_1/postgresql13_1-z9af3jew.html
- trust: unconditionally allow connections. Allow any user who can connect to the PostgreSQL database server to log in as any PostgreSQL database user they want without password or any other authentication.
- Reject: unconditionally reject the connection. This helps to "filter out" specific hosts from a group. For example, a reject line can block a specific host connection, while the latter line allows the rest of the hosts in a specific network to connect.
- Scram-sha-256: perform scram-sha-256 authentication to verify the user's password.
- md5: the client is required to provide an md5 encrypted password for authentication. This method allows the encrypted password to be stored in PG_ The only way in shadow.
- Password: the client is required to provide an unencrypted password for authentication. Because passwords are sent in clear text on the network, we should not use this method on untrusted networks.
- gss: use GSSAPI to authenticate users. Available only for TCP/IP connections. It can be used in conjunction with GSSAPI encryption.
- SSPI: use SSPI to authenticate users. Available only on Windows.
- Ident: obtain the operating system name of the client by contacting the ident server of the client, and check whether it matches the requested database user name. Ident authentication can only be used on TCIP/IP connections. When this authentication method is specified for local connections, peer authentication will be used instead.
- peer: works by obtaining the operating system user name of the client from the kernel and using it as the allowed database user name (and optional user name mapping). Like Ident, this method is only supported on local connections.
- LDAP: use LDAP server authentication.
- RADIUS: use RADIUS server for authentication.
- cert: use SSL client certificate authentication.
- PAM: use the pluggable authentication module service (PAM) provided by the operating system to authenticate.
- BSD: use the BSD authentication service provided by the operating system for authentication.
PostgreSQL client authentication (access control) is usually defined in pg_hba.conf file. By default, for local connections, PostgreSQL is set to prevent peer authentication.
To log in to the PostgreSQL server as a PostgreSQL user, first switch users, and then use the psql tool to access PostgreSQL.
sudo su - postgres psql
Or use the sudo command to access PostgreSQL:
sudo -u postgres psql
Note: postgres users are only used locally.
$4 | create PostgreSQL roles and databases
Only superusers and roles with creator ole permission can create new roles.
For example: we create a role named john and a database named johndb, and grant permissions on the database.
- 4.1 connecting to PostgreSQL shell
sudo -u postgres psql
- 4.2 create a new PostgreSQL role
sudo -u postgres psql
- 4.3 create a new database
create database johndb
- 4.4 grant users all permissions on the database through the following query statement
grant all privileges on database johndb to john
$5 | enable remote access to PostgreSQL server
By default, the PostgreSQL server only listens to the local network interface: 127.0.0.1
To allow remote access to your PostgreSQL server, open the configuration file:
# Find PostgreSQL Conf location sudo find postgresql.conf # Edit PostgreSQL Conf file sudo vim /var/lib/pgsql/data/postgresql.conf /CONNECTIONS AND AUTHENTICATION
Slide down to the CONNECTIONS AND AUTHENTICATION section and add or edit the following line:
#------------------------------------------------------------------------------ # CONNECTIONS AND AUTHENTICATION #------------------------------------------------------------------------------ # - Connection Settings - listen_addresses = '*' # what IP address(es) to listen on;
Save the file and restart the PostgreSQL service with the following command:
:wq! sudo systemctl restart postgresql
Use the ss tool to verify this modification:
[root@TENCENT64 /]# ss -nlt | grep 5432 LISTEN 0 244 0.0.0.0:5432 0.0.0.0:* LISTEN 0 244 [::]:5432 [::]:*
The above output shows that the PostgreSQL server is already listening for the default ports of all network interfaces (0.0.0.0).
The last step is to edit pg_hba.conf file to configure the server to accept remote connections. Here are some examples showing different user examples:
[root@TENCENT64 /]# ls /var/lib/pgsql/data base log pg_hba.conf pg_multixact pg_serial pg_stat_tmp pg_twophase pg_xact postmaster.opts current_logfiles pg_commit_ts pg_ident.conf pg_notify pg_snapshots pg_subtrans PG_VERSION postgresql.auto.conf postmaster.pid global pg_dynshmem pg_logical pg_replslot pg_stat pg_tblspc pg_wal postgresql.conf [root@TENCENT64 /]# sudo vim /var/lib/pgsql/data/pg_hba.conf
pg_ hba. The default configuration of conf is as follows:
# TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all peer # IPv4 local connections: host all all 127.0.0.1/32 ident # IPv6 local connections: host all all ::1/128 ident # Allow replication connections from localhost, by a user with the replication privilege. local replication all peer host replication all 127.0.0.1/32 ident host replication all ::1/128 ident
Amend to read as follows:
# TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all peer # IPv4 local connections: host all all 0.0.0.0/0 trust # IPv6 local connections: host all all ::1/128 ident # Allow replication connections from localhost, by a user with the replication privilege. local replication all peer host replication all 0.0.0.0/0 trust host replication all ::1/128 ident
Finally, we use the DBeaver client tool to access the link test, as shown below:
$6 | summary
tlinux 3.1 is a release based on RedHat CentOS release. By default, it supports PostgreSQL version 9.6/10/12/13. The kernel information is as follows:
WARNING! The remote SSH server rejected X11 forwarding request. Welcome to TencentOS 3 64bit Version 3.1 20211028 Last login: Fri Jan 28 17:13:06 2022 from 192.168.10.251 [root@TENCENT64 ~]# uname -a Linux TENCENT64.site 5.4.119-19-0008 #1 SMP Mon Sep 13 14:54:39 CST 2021 x86_64 x86_64 x86_64 GNU/Linux [root@TENCENT64 ~]# cat /etc/redhat-release CentOS Linux release 8.4.2105 (Core) [root@TENCENT64 ~]#
For more PostgreSQL version installation, please check = > https://www.postgresql.org/docs/14/index.html