Centos環境でのPostgresqlのインストールと設定、環境変数の設定Tips

The above creates the database instance
sudo /usr/pgsql-13/bin/postgresql-13-setup initdb

Allow the postgresql database to start on boot
sudo systemctl enable postgresql-13

The service management command of the operating system starts the database
sudo systemctl start postgresql-13

Stop the database command
sudo systemctl stop postgresql-13

View database status command
sudo systemctl status postgresql-13

tar -xvf postgresql-13.tar.gz

 cd postgresql-13

. /configure --prefix=/usr/local/pgsql13 --with-perl --with-python
make
make install
 
Command Explanation
--prefix=/usr/local/pgsq13 determines its installation directory
The --with-perl option allows you to write custom functions in the PL/Perl procedure language using the perl syntax. To use this option, you need to install the perl package, which is called libperl-dev under ubantu or Debian
 
--with-python Add this option to write custom functions in the Py/Python procedural language using the python syntax. To use this option you need to install the python-devk development package first.

groupadd postgres
useradd -g postgres postgres

cd /usr/local
sudo ln -sf /usr/local/pgsql13 /usr/local/pgsql
 
Command Explanation
The path set by --prefix is /usr/local/pgsql13. If you do not set the path, the default path is /usr/local/.
The path is created with the version number to facilitate future upgrades
To upgrade to a higher version of the database, just stop the current database, compile the higher version of the database postgreslq14, and point the link /usr/local/pgsql to the new version of the directory /usr/local/pgsql14 to complete the upgrade

mkdir /usr/local/pgsql/data
cd /usr/local/
chown postgres.postgres pgsql

Switching users
 
su - postgresql
 
Initialize data -D Specify the file path to initialize the created database
 
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data

pg_ctl start
pg_ctl stop

Add the following to /etc/profile or ~/.bash_profile
 
export PGHOME=/usr/local/pgsql   
export PGDATA=/usr/local/pgsql/data
export LD_LIBRARY_PATH=/usr/local/pgslq/lib
export PATH=$PATH:$HOME/bin:/usr/local/pgsql/bin
 
Command Explanation
The order in which the configuration file is read when bash logs in is as follows:
 
.bash_profile
.bash_login
.profile
 
In this order, as soon as one is read, the other two are not read. The global /etc/profile is the first to be read, and the configuration in /etc/profile can be overwritten in the above three files.

The database created by default cannot accept remote links add the following command to pg_hba.conf
host all all 0/0 md5
 
This command allows any account to connect to the database remotely, and requires a password to connect.
pg_hba.conf is a black and white list access control file that controls which IP addresses are allowed to access the database

Edit the postgres.conf file in the data directory
Modify
# listen_addresses = 'localhost'
# port = 5432
 
The listen_addresses parameter represents the listening address, which by default is at localhost. This will cause the remote host to be inaccessible, so you need to change the listening address to the actual network address, a simple way is to change the address to '*'
listen_addresses = '*'
 
The parameter port indicates the port of the database, the default is 5432, if a machine with multiple database instances installed, the author is on docker also has a data service, occupying the port 5432. So the host is modified
 
For the above two parameters, you need to restart the database to generate the pin

If you change the host port, you need to add a line to /etc/profile or ~/.bash_profile
exprot PGHOST=new port