01-PostgreSQL installation and remote connection

1. Windows installation PostgreSQL

①：Download

1. Official website download

Address: https://www.postgresql.org/download/

• Choose the corresponding system

• Click to download

• Select the version to download (I downloaded version 14.X here)

• Download completed

2. Network disk download

Link: https://pan.baidu.com/s/1u3ZcgWb6Woh_0n0ZV2PG9A?pwd=Coke

Extraction code: Coke

②: Installation

1. You can first create two folders to store the installation directory and data respectively

2. Just double-click to run

3.next

4. Select the installation directory and then next

5. Select components, select all by default, then next

6. Select the data storage path and then next

7. Set a password for the database super user postgres, then next

8. Set the port, the default is enough, and then next

9. Select the locale, the default is enough, and then next

10. Install the above settings, then next

11. Install

12. After the installation is complete, check the box to start the stack generator and click finish

13. The stack generator installation interface pops up, select the previously installed software (the computer must be connected to the Internet), and click next

14. The installation application interface pops up, select the language pack to install, and click next

15. Set the installation path and then next step

16. Click Next (uncheck, skip installation)

17. The installation language option pops up, click OK

18. Click next

19. Install next

③: Test connection

1. Using Navicat

2. Use idea

④: Set up remote connection

2. Install PostgreSQL on Ubuntu

①: Download command

1. Official website: https://www.postgresql.org/download/

②: Execute download command

1. Install the specified version of PostgreSQL

sudo apt install postgresql-14

③: Modify the default database password

1. PostgreSQL will create a postgres user as the administrator account by default, and the password is random
2. Use sudo -u postgres psql to log in to the database. This method does not require a password
3. Enter alter user postgres with password 123456’; after the postgres=# prompt appears.
4. Change the password. After the operation is successful, enter \q to exit psql

1. Use sudo -u postgres psql to log in to the database

2. Change password: alter user postgres with password 123456’;

3. \q exit psql

④: Log in with new password

Logged in with new password psql -U postgres -d postgres -h 127.0.0.1 -p 5432

⑤: Set to allow remote access

1. Modify the listening address

Command: sudo vim /etc/postgresql/14/main/postgresql.conf

Remove the comment from #listen_addresses = 'localhost' and change it to listen_addresses = '*'

2. Modify accessible IP segments

• Tips: Modify file permissions: sudo chmod -R 777 *

Command: sudo vim /etc/postgresql/14/main/pg_hba.conf

Add a line at the following location to allow remote access from any address through password

3. Restart the database

sudo service postgresql restart

4. Navicat test connection

3. Installing PostgreSQL in CentOS

①: Download command

1. View system architecture uname -m

2. Official website: https://www.postgresql.org/download/

# Install repository RPM:
yum install -y https://download.postgresql.org/pub/repos/yum/reporrpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# Install PostgreSQL
yum install -y postgresql14-server

# Optionally initialize the database and enable automatic startup:
/usr/pgsql-14/bin/postgresql-14-setup initdb
systemctl enable postgresql-14
systemctl start postgresql-14

②: Start installation

1. Install repository RPM

yum install -y https://download.postgresql.org/pub/repos/yum/reporrpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

2. Install PostgreSQL

yum install -y postgresql14-server

3. You can choose to initialize the database and enable automatic startup

/usr/pgsql-14/bin/postgresql-14-setup initdb
systemctl enable postgresql-14
systemctl start postgresql-14

③:Set password

1. Switch to postgres user

su-postgres

2. Enter psql

3. Modify the postgres superuser password

alter user postgres with password 'new password';

4. Use exit to exit

④: Set up remote permission connection

1. Modify the postgresql.conf file

Enable remote access and change #listen_addresses = 'localhost' to listen_addresses = '*'

vim /var/lib/pgsql/14/data/postgresql.conf

2. Modify the pg_hba.conf file

Add a line host all all 0.0.0.1/0 scram-sha-256

3. Restart the database service

systemctl restart postgresql-14

4. Log in with new password

psql -U postgres -h 127.0.0.1

5. Use Navicat to test the connection