PostgreSQL Tutorial

PostgreSQL Tutorial#

The PostgreSQL object-relational database system provides reliability and data integrity.

Docker#

docker run -d --name pg01 -p 5432:5432 --restart=always -e POSTGRES_DB=pg -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=postgres -v /usr/local/postgres/pg01:/var/lib/postgresql/data -v /etc/localtime:/etc/localtime:ro postgres:11.9

via psql#

docker run -it --rm postgres:11.9 psql -h pg01_IP -U postgres

CentOS 7#

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 postgresql11-server
sudo /usr/pgsql-11/bin/postgresql-11-setup initdb
sudo systemctl enable postgresql-11
sudo systemctl start postgresql-11

开启远程连接

vi /var/lib/pgsql/11/data/postgresql.conf
listen_addresses = '*'
vi /var/lib/pgsql/11/data/pg_hba.conf
host    all             all             0.0.0.0/0               trust
systemctl restart postgresql-11

pg_dump#

要把一个数据库mydb转储到一个 SQL 脚本文件:

pg_dump mydb > db.sql

要把这样一个脚本重新载入到一个(新创建的)名为newdb的数据库中:

psql -d newdb -f db.sql

要转储一个名为mytab的表:

pg_dump -t mytab mydb > db.sql

Tests#

createdb mydb
# dropdb mydb
psql mydb
SELECT version();
SELECT current_date;
SELECT 2 + 2;
\h
\q

References#