PostgreSQL安装使用

Centos7安装Postgres13

安装

sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo yum install -y postgresql13-server
sudo /usr/pgsql-13/bin/postgresql-13-setup initdb
sudo systemctl enable postgresql-13
sudo systemctl start postgresql-13

ps -ef | grep postgres

允许远程访问认证

vi /var/lib/pgsql/13/data/pg_hba.conf
查找
host    all             all             0.0.0.0/0                trust

vi /var/lib/pgsql/13/data/postgresql.conf
查找listen_addresses
listen_addresses = '*'

重启postgres
sudo systemctl restart postgresql-13

Postgres使用

创建用户数据库

sudo -u postgres psql

create user USER with password '[密码]';
create database DB_NAME owner USER;
grant all on database DB_NAME to USER;

删除数据库/用户

drop database DB_NAME;
drop role USER;

数据转储

备份数据库结构和数据

su - postgres
pg_dump -Fc -f DB_NAME.sql DB_NAME

备份数据库结构和数据-不带表空间信息

su - postgres
pg_dump --no-tablespaces -Fc -f DB_NAME_20211119.sql DB_NAME

备份指定表

pg_dump -Fc -s -t TABLE_NAME -f DB_NAME.sql DB_NAME

恢复结构和数据

su - postgres
pg_restore -d DB_NAME DB_NAME.sql

恢复数据到远程服务器
pg_restore -h HOST_IP -p 5432 -U USER -d DB_NAME /var/lib/pgsql/DB_NAME.sql