Install & configure PostgreSQL 12 on CentOS 7
I. Introduction
1. Main content of this article
PostgreSQL 12 installation (yum)
PostgreSQL 12 basic configuration
PostgreSQL 12 Remote Access Configuration
PostgreSQL Basic Administration
2. Environmental information and scope of application of this article
environmental information
software | Version |
---|---|
CentOS | 7.6 Release |
PostgreSQL | 12.x |
scope of application
software | Version |
---|---|
CentOS | CentOS 7.x |
PostgreSQL | 9.x-12.x |
2. PostgreSQL installation
1. Import yum source
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
1. Install PostgreSQL service
sudo yum install -y postgresql12 postgresql12-server
Installing PostgreSQL 11 is yum install postgresql12 postgresql12-server
Installing PostgreSQL 9.5 is yum install postgresql95 postgresql95-server
and so on
2. Initialize the database
sudo /usr/pgsql-12/bin/postgresql-12-setup initdb #Initializing database... OK
3. Start the PostgreSQL service
#Start the PostgreSQL service sudo systemctl start postgresql-12 #Set the PostgreSQL service to start at boot sudo systemctl enable postgresql-12
The service name of the 9.x version is postgresql-9.x
2. Modify the postgres account password
After PostgreSQL is successfully installed, a Linux user named postgres will be created by default. After the database is initialized, there will be a database named postgres to store the basic information of the database, such as user information, etc., which is equivalent to the default database named mysql in MySQL. .
A super user will be initialized in the postgres databasepostgres
In order to facilitate us to use the postgres account for management, we can modify the password of the account
1. Enter the PostgreSQL command line
Switching the linux user to postgres through the su command will automatically enter the command line
su postgres
2. Start SQL Shell
psql
3. Change password
ALTER USER postgres WITH PASSWORD 'NewPassword';
3. Configure remote access
1. Open port
sudo firewall-cmd --add-port=5432/tcp --permanent sudo firewall-cmd --reload
2. Modify IP binding
#Modify the configuration file vi /var/lib/pgsql/12/data/postgresql.conf #Change the listening address to * #The default listen_addresses configuration is commented out, so you can add this line directly at the beginning of the configuration file listen_addresses='*'
3. Allow all IP access
#Modify the configuration file vi /var/lib/pgsql/12/data/pg_hba.conf # Add at the end of the asking price host all all 0.0.0.0/0 md5
4. Restart the PostgreSQL service
#Restart the PostgreSQL service sudo systemctl restart postgresql-12
After the configuration is complete, you can use the client to connect
Four, PostgreSQL shell common syntax examples
Start the SQL shell:
su postgres psql
1. Examples of database-related syntax
#create database CREATE DATABASE mydb; #View all databases \l #Switch the current database \c mydb #create table CREATE TABLE test(id int, body varchar(100)); #View all tables in the current database \d
2. Example of user and access authorization syntax
# create a new user CREATE USER test WITH PASSWORD 'test'; #Give the specified account all permissions to the specified database GRANT ALL PRIVILEGES ON DATABASE mydb TO test; #Remove all permissions of the specified account and specified database REVOKE ALL PRIVILEGES ON DATABASE mydb TO test
Permission codes: SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, CREATE, CONNECT, TEMPORARY, EXECUTE, USAGE
5. Remarks
1. Related reading
https://www.postgresql.org/download/
https://www.runoob.com/postgresql/postgresql-privileges.html
0 Comments