centos7 postgresql 安装&操作

安装psql

[root@atom ~]#sudo yum install -y \
https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-
[root@atom ~]#sudo yum install -y postgresql12 postgresql12-server
[root@atom ~]#sudo /usr/pgsql-12/bin/postgresql-12-setup initdb
Initializing database ... OK
[root@atom ~]#systemctl status postgresql-12.service
[root@atom ~]#systemctl start postgresql-12

[root@atom ~]#su postgres
[postgres@atom root]$psql
could not change directory to "/root": Permission denied
psql (12.12)
Type "help" for help.
postgres=# ALTER USER postgres WITH PASSWORD '123456';
ALTER ROLE
postgres=# \q
[postgres@atom root]$exit

配置远程连接

[root@atom ~]#vim /var/lib/pgsql/12/data/postgresql.conf
listen_addresses='*'			#更改为*
[root@atom ~]#vim /var/lib/pgsql/12/data/pg_hba.conf
host  all  all 0.0.0.0/0 md5       #末尾添加
[root@atom ~]#sudo systemctl restart postgresql-12
[root@atom ~]#sudo firewall-cmd --add-port=5432/tcp --permanent
success
[root@atom ~]#sudo firewall-cmd --reload
success
[root@atom ~]#sudo systemctl restart postgresql-12

基础

ALTER USER postgres WITH PASSWORD 'NewPasswd';

sudo systemctl restart postgresql-12

建库

\l #列数据库
[postgres@atom ~]$psql -l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 tomdb     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(4 rows)
[postgres@atom ~]$createdb tomdb     #创建数据库
[postgres@atom ~]$psql
psql (12.12)
Type "help" for help.
#\c + 数据库名		# 来进入数据库：
postgres=# \c tomdb
You are now connected to database "tomdb" as user "postgres".
tomdb=# 

建表

#db.sql
create table posts (
id serial primary key,
title varchar(255) not null,
content text check(length (content) >8),
is_draft boolean default TRUE,
is_del boolean default FALSE,
created_date timestamp  default  'now'
);


\i db.sql       //1、sql

tomdb=# create table posts (title varchar(255), content text);    //2.命令行
CREATE TABLE
tomdb=# 

tomdb=# insert into posts (title,content) values ('this','welcome to postgresql'); 
INSERT 0 1
tomdb=# 

\x			#切换横纵向显示

 \dt	#disply table，列出各个表
tomdb=# \dt
          List of relations
 Schema |   Name   | Type  |  Owner   
--------+----------+-------+----------
 public | posts    | table | postgres
 public | tonixtom | table | postgres
(2 rows)
\d tonixtom #看表结构

tomdb=# \d tonixtom
                      Table "public.tonixtom"
 Column  |          Type          | Collation | Nullable | Default 
---------+------------------------+-----------+----------+---------
 title   | character varying(255) |           |          | 
 content | text                   |           |          | 

alter table posts rename to tonixtom;    #rename table

https://ken.io/note/centos7-postgresql12-install-and-configuration