Linux system: building PostgreSQL relational database under Centos7

Source code: GitHub point here || GitEE point here

1, Introduction to PostgreSQL

1. Database introduction

PostgreSQL is a powerful open source database system, which has the characteristics of reliability, stability, data consistency, and can run on all major operating systems, including Linux, Unix, Windows, etc. PostgreSQL is a complete transaction security database, which fully supports foreign keys, unions, views, triggers and stored procedures. It supports most of the data types of SQL:2008 standard, including integer, numeric, Boolean, byte, character, date, time interval and time. It also supports the storage of binary large pairs, including pictures, sounds and videos . For many high-level development languages, there are native programming interface API s, such as C/C + +, Java, etc., which also contain various documents.

2. Highly open source

The source code of PostgreSQL can be obtained freely. Its authorization is under the very free open source authorization, which allows users to use, modify and publish the source code of PostgreSQL in various open source or closed source projects. Users can modify and improve the source code according to their wishes. Therefore, PostgreSQL is not only a powerful enterprise database system, but also a database development platform for users to develop private, network and commercial software products.

2, Centos7 installation

1. Install RPM

RPM package manager, a packaging and installation tool for Internet download packages, is included in some Linux distributions.

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

2. Install client

yum install postgresql11

3. Install server side

yum install postgresql11-server

4. Install dependency package

yum install postgresql11-libs
yum install postgresql11-contrib
yum install postgresql11-devel

5. Initialization and startup

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

6. Reset password

passwd postgres

7. Login service

su - postgres
psql

8. Install Vim command

yum -y install vim*

9. Configure remote access

# Amendment 01
vim /var/lib/pgsql/11/data/postgresql.conf
listen_addresses = 'localhost' 
//Modified to
listen_addresses = '*'  

# Amendment 02
vim /var/lib/pgsql/11/data/pg_hba.conf
//Add content
host  all  all  0.0.0.0/0 trust ## Need to restart after modification

10. Open port

firewall-cmd --query-port=5432/tcp

firewall-cmd --add-port=5432/tcp

firewall-cmd --add-port=5432/tcp --zone=public --permanent

11. Restart

systemctl restart postgresql-11

3, Create database

1. Create user

CREATE USER root01 WITH PASSWORD '123456';
CREATE ROLE;

2. Create database

CREATE DATABASE db_01 OWNER root01;
CREATE DATABASE;

3. Permission granting

GRANT ALL PRIVILEGES ON DATABASE db_01 TO root01;
GRANT

4. Exit command

\q: Exit SQL edit
 Exit: exit script

4, Basic operation

1. Create table structure

-- User table
CREATE TABLE pq_user (
	ID INT NOT NULL,
	user_name VARCHAR (32) NOT NULL,
	user_age int4 NOT NULL,
	create_time TIMESTAMP (6) DEFAULT CURRENT_TIMESTAMP,
	CONSTRAINT "pg_user_pkey" PRIMARY KEY ("id")
);

-- Order form
CREATE TABLE pq_order (
	id int not null,
	user_id int not null,
	order_no varchar (32) not null,
	goods varchar (20) not null,
	price money not null,
	count_num int default 1, 
	create_time timestamp (6) default current_timestamp,
	constraint "pq_order_pkey" primary key ("id")
);

2. Write data

INSERT INTO pq_user ("id", "user_name", "user_age", "create_time") 
VALUES ('1', 'user01', '18', '2020-04-09 19:44:57.16154');
INSERT INTO pq_order ("id", "user_id", "order_no", "goods", "price", "count_num", "create_time") 
VALUES ('1', '1', 'NO20200329652362', 'book', '$12.20', '3', '2020-04-09 20:01:09.660208');

3. General query

-- Basic query
select * from pq_user t1 where t1.id='2' and t1.user_name='user01';
select * from pq_user t1 where t1.id !='2' order by create_time desc;
-- join query
select * from pq_user t1 join pq_order t2 on t1.id=t2.user_id;
select * from pq_user t1 left join pq_order t2 on t1.id=t2.user_id;

4. Update and delete

-- Update data
UPDATE pq_user SET "create_time"='2020-04-09 19:49:57' WHERE ("id"='2');
-- Delete record
DELETE FROM pq_user WHERE "id" = 2;

5, Source code address

GitHub·address
https://github.com/cicadasmile/linux-system-base
GitEE·address
https://gitee.com/cicadasmile/linux-system-base

Posted by MaxBodine on Sat, 11 Apr 2020 21:55:33 -0700

Programmer Group