安装
下载对应的安装包安装即可。这里在Ubuntu下安装
apt install -y postgresql-11
程序目录
- whereis postgresql 查询文件分布目录
- /usr/lib/postgresql/11/存放命令 和lib库
- /etc/postgresql 配置文件
- /usr/include/postgresql C/C++头文件
- /usr/share/postgresql 文档 man 示例文件 扩展
命令
-rwxr-xr-x 1 root root 68096 Jan 30 20:23 clusterdb*
-rwxr-xr-x 1 root root 68160 Jan 30 20:23 createdb*
-rwxr-xr-x 1 root root 72608 Jan 30 20:23 createuser*
-rwxr-xr-x 1 root root 63904 Jan 30 20:23 dropdb*
-rwxr-xr-x 1 root root 63872 Jan 30 20:23 dropuser*
-rwxr-xr-x 1 root root 142528 Jan 30 20:23 initdb* 创建新的数据库目录
-rwxr-xr-x 1 root root 26616 Jan 30 20:23 oid2name*
-rwxr-xr-x 1 root root 26696 Jan 30 20:23 pg_archivecleanup*
-rwxr-xr-x 1 root root 121920 Jan 30 20:23 pg_basebackup*
-rwxr-xr-x 1 root root 39072 Jan 30 20:23 pg_controldata*
-rwxr-xr-x 1 root root 51696 Jan 30 20:23 pg_ctl*
-rwxr-xr-x 1 root root 401280 Jan 30 20:23 pg_dump*
-rwxr-xr-x 1 root root 97704 Jan 30 20:23 pg_dumpall*
-rwxr-xr-x 1 root root 63808 Jan 30 20:23 pg_isready*
-rwxr-xr-x 1 root root 72456 Jan 30 20:23 pg_receivewal*
-rwxr-xr-x 1 root root 72560 Jan 30 20:23 pg_recvlogical*
-rwxr-xr-x 1 root root 51696 Jan 30 20:23 pg_resetwal*
-rwxr-xr-x 1 root root 171456 Jan 30 20:23 pg_restore*
-rwxr-xr-x 1 root root 84384 Jan 30 20:23 pg_rewind*
-rwxr-xr-x 1 root root 26632 Jan 30 20:23 pg_standby*
-rwxr-xr-x 1 root root 30912 Jan 30 20:23 pg_test_fsync*
-rwxr-xr-x 1 root root 26776 Jan 30 20:23 pg_test_timing*
-rwxr-xr-x 1 root root 137840 Jan 30 20:23 pg_upgrade*
-rwxr-xr-x 1 root root 39104 Jan 30 20:23 pg_verify_checksums*
-rwxr-xr-x 1 root root 84480 Jan 30 20:23 pg_waldump*
-rwxr-xr-x 1 root root 146744 Jan 30 20:23 pgbench* 基准测试工具
-rwxr-xr-x 1 root root 7682416 Jan 30 20:23 postgres*
lrwxrwxrwx 1 root root 8 Jan 30 20:23 postmaster -> postgres*
-rwxr-xr-x 1 root root 658688 Jan 30 20:23 psql*
-rwxr-xr-x 1 root root 72288 Jan 30 20:23 reindexdb*
-rwxr-xr-x 1 root root 76480 Jan 30 20:23 vacuumdb*
-rwxr-xr-x 1 root root 26616 Jan 30 20:23 vacuumlo*
初始化
mkdir -R /pgdata/11/{postgres_data,backups,scripts,archive_wals} 创建数据目录
chown postgres.postgres ./*
chmod 700 ./*
sudo su postgres 切换用户
/usr/lib/postgresql/11/bin/initdb -D /pgdata/11/postgres_data/ -W 初始化
启动服务
/usr/lib/postgresql/11/bin/pg_ctl -D /pgdata/11/postgres_data/ -l logfile start 启动
错误提示：
waiting for server to start....2019-04-29 16:08:24.052 DST [11587] LOG: listening on IPv4 address "127.0.0.1", port 5432
2019-04-29 16:08:24.098 DST [11587] FATAL: lock file "/var/run/postgresql/.s.PGSQL.5432.lock" is empty
2019-04-29 16:08:24.098 DST [11587] HINT: Either another server is starting, or the lock file is the remnant of a previous server startup crash.
2019-04-29 16:08:24.105 DST [11587] LOG: database system is shut down
错误说明：端口号被占用
解决：
vim postgresql.conf 修改port为5434等端口号，再次执行启动即可。
ps aux|grep postgres 查看服务是否启动。
/usr/lib/postgresql/11/bin/pg_isready -p 5434 查看是否可接受连接
/usr/lib/postgresql/11/bin/pg_ctl -D /pgdata/11/postgres_data/ [-ms|-mf|-mi] stop 停止数据库服务
/usr/lib/postgresql/11/bin/pg_ctl -D /pgdata/11/postgres_data/ reload 重启数据库服务
数据库配置
PG_VERSION pg_commit_ts pg_ident.conf pg_notify pg_snapshots pg_subtrans pg_wal postgresql.conf
base pg_dynshmem pg_logical pg_replslot pg_stat pg_tblspc pg_xact postmaster.opts
global pg_hba.conf pg_multixact pg_serial pg_stat_tmp pg_twophase postgresql.auto.conf postmaster.pid
postgresql.conf 负责配置文件位置，资源限制，集群复制等，是全局配置文件
pg_hba.conf 客户端的连接和用户认证，是全局配置文件
错误说明：启动报错
PANIC: could not flush dirty data: Function not implemented
解决：
vim postgresql.conf
选项
data_sync_retry = on
重启即可
客户端工具
psql -h 127.0.0.1 -p 5434 -d postgres postgres 连接数据库
create tablespace tbs_mydb owner postgres location '/database/pg11/pg_tbs/tbs_mydb'; 创建表空间
create database mydb with owner postgres encoding = 'UTF8' tablespace = tbs_mydb; 创建数据库
psql元命令
\db
\d
\sf
\h 帮助
\? 元命令说明
\timing 开关显示sql执行时间
\watch 反复执行上一句sql
这里不多赘述。
创建数据表
create table test_copy(id int,name text);
导出表数据
copy test_copy to '/tmp/test_copy.csv';
导入表数据
copy test_copy from '/tmp/test_copy.csv';
下一节将介绍pgsql的数据类型、sql操作、索引的使用。
