Postgres基础篇

PostgreSQL基础篇

yum安装PostgreSQL

• 具体安装命令

yum install -y epel-release
yum install -y postgresql-server postgresql-contrib
postgresql-setup initdb
systemctl start postgresql
systemctl enable postgresql
psql --version
systemctl status postgresql
su - postgres
[postgres@cdesk_node_2 ~]$  psql --version
psql (PostgreSQL) 9.2.24

PostgreSQL基本设置

• 修改密码设置远程访问

[postgres@cdesk_node_2 ~]$ psql  
psql (9.2.24)
Type "help" for help.

postgres=# ALTER USER postgres WITH PASSWORD 'd@a88SZVCn72'; ALTER ROLE

#修改postgresql.conf 配置文件
[postgres@cdesk_node_2 ~]$ vim /var/lib/pgsql/data/postgresql.conf #修改远程访问
listen_addresses = '*'          # what IP address(es) to listen on;

#修改新增配置项pg_hba.conf文件
host    all             all             127.0.0.1/32           ident
host    all             all              0.0.0.0/0              md5

• 重启PostgreSQL服务,并远程连接PostgreSQL数据库

[root@cdesk_node_2 ~]# systemctl restart postgresql.service
[root@cdesk_node_2 ~]# psql -h10.130.41.128 -Upostgres -W
#输入密码d@a88SZVCn72
Password for user postgres: 
psql (9.2.24)
Type "help" for help.

postgres=# \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
(3 rows)

• 创建用户，并给超级权限

#创建用户时指定SUPERUSER选项。这将使得该用户拥有所有权限，包括管理数据库和用户的权限
CREATE USER ucds WITH PASSWORD 'd@a88SZVCn72' SUPERUSER;

• 创建数据库并使用多个SCHEMA

postgres=# CREATE DATABASE ucds;




#链接到ucds库中
postgres=# \c ucds;
Password for user ucds: 
You are now connecte