pgsql操作数据库命令

74 阅读 0 评论 49 点赞

我是靠谱客的博主落寞白猫，最近开发中收集的这篇文章主要介绍pgsql操作数据库命令，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

关于postgresql——常用操作指令
创建数据库
CREATE DATABASE test WITH OWNER = postgres ENCODING = 'UTF8';

进入控制台方法，在postgreSQL的安装目的bin下执行命令： psql 数据库名，
例： /usr/local/pgsql/bin/psql mydb

指定host、用户名和数据库的话，如：
/usr/local/pgsql/bin/psql -h localhost -U postgres -d test

查看版本： psql --version 或 SELECT version();

查看所有数据库：l

查看所有数据库（包括详细参数）：select * from pg_database;

选择数据库：c databasename

查看所有表：dt schema_name.*

查看某个表的结构：d schema_name.tablename

退出psql控制台：q

查看表的索引：
select * from pg_indexes where tablename='log';

导出备份数据库：
pg_dump -h localhost -U postgres databasename > /tmp/databasename.bak.yyyymmdd.sql
(或者用如下命令：
#su postgres

#psql

说明：

1.(psql -U immuser -h 127.0.0.1 -d newpro 表示使用newpro数据库的immuser用户登录本地服务器的newpro数据库

2.（psql -U postgres -h 127.0.0.1 表示使用postgres 用户登录本地数据库）)
#l
#q
#cd (这里cd后面直接按回车键，表示进入到postgres用户的home目录，linux是多用户的，cd后面不加任何参数表示，进入当前用户的家目录中)
#pg_dump databasename > databasename.sql
)

下面是数据库备份与恢复详细过程：

导入恢复数据库(sql文件是pg_dump导出的文件就行，可以是整个数据库，也可以只是单个表，也可以只是结构等)：
mkdir -p /var/lib/pgsql/9.4/data/newpro_space #创建存放表空间的目录
chown -R postgres:postgres /var/lib/pgsql/9.4/data/newpro_space #修改其所属组
(1).创建表空间
CREATE TABLESPACE newpro_sp
OWNER immuser
LOCATION '/var/lib/pgsql/9.4/data/newpro_space';
-- Database: usercenter
-- DROP DATABASE usercenter;

(2).创建数据库，因为备份数据库时只能备份表的结构和内容，所以在回复数据库的时候要先创建数据库，用于恢复数据；
CREATE DATABASE newpro
WITH OWNER = immuser
ENCODING = 'UTF8'
TABLESPACE = newpro_sp
LC_COLLATE = 'en_US.UTF-8'
LC_CTYPE = 'en_US.UTF-8'
CONNECTION LIMIT = -1;
psql -h localhost -U postgres -d databasename < /tmp/databasename.bak.yyyymmdd.sql
(或者用如下命令：
#su postgres
#cd (这里cd后面直接按回车键，表示进入到postgres用户的home目录，linux是多用户的，cd后面不加任何参数表示，进入当前用户的家目录中)
psql databasename < databasename.sql
)

导出数据结构，主要是加上参数-s：
pg_dump -U username -W dbname -f /tmp/filename.sql

导出某个表：
pg_dump -h localhost -U postgres -t tablename dbname > test.sql

导出某个表的结构，同样是加参数"-s"：
pg_dump -h localhost -U postgres -t tablename -s dbname > test_construct.sql

导出某个表的数据，加参数"-a"：
pg_dump -h localhost -U postgres -t tablename -a dbname > test_data.sql

查看序列：select * from information_schema.sequences where sequence_schema = 'public';

查看数据库大小：select pg_size_pretty(pg_database_size('test'));

查看表的大小：select pg_size_pretty(pg_relation_size('test'));

####为了便于数据库的统一管理与减少pgsql的安装将三台服务器数据库全部搭建在87服务器上####
###10.0.1.87###
首先要先将101和70服务器上的数据库备份.sql文件上传到87服务器上；
然后按照如下步骤操作：

a).恢复newpro数据库：
mkdir -p /var/lib/pgsql/9.4/data/newpro_space #创建存放表空间的目录
chown -R postgres:postgres /var/lib/pgsql/9.4/data/newpro_space #修改其所属组
#su postgres
#psql
(1).创建表空间
CREATE TABLESPACE newpro_sp
OWNER immuser
LOCATION '/var/lib/pgsql/9.4/data/newpro_space';
-- Database: newpro;
-- DROP DATABASE newpro;

(2).创建数据库，因为备份数据库时只能备份表的结构和内容，所以在回复数据库的时候要先创建数据库，用于恢复数据；
CREATE DATABASE newpro
WITH OWNER = immuser
ENCODING = 'UTF8'
TABLESPACE = newpro_sp
LC_COLLATE = 'en_US.UTF-8'
LC_CTYPE = 'en_US.UTF-8'
CONNECTION LIMIT = -1;
#q
psql -h localhost -U postgres -d newpro< newpro.sql
(或者用如下命令：
#su postgres
#cd (这里cd后面直接按回车键，表示进入到postgres用户的home目录，linux是多用户的，cd后面不加任何参数表示，进入当前用户的家目录中)
psql newpro< newpro.sql
)

b).恢复usercenter数据库：
mkdir -p /var/lib/pgsql/9.4/data/usercenter_space #创建存放表空间的目录
chown -R postgres:postgres /var/lib/pgsql/9.4/data/usercenter_space #修改其所属组
#su postgres
#psql
(1).创建表空间
CREATE TABLESPACE usercenter_sp
OWNER immuser
LOCATION '/var/lib/pgsql/9.4/data/usercenter_space';
-- Database: usercenter;
-- DROP DATABASE usercenter;

(2).创建数据库，因为备份数据库时只能备份表的结构和内容，所以在回复数据库的时候要先创建数据库，用于恢复数据；
CREATE DATABASE usercenter
WITH OWNER = immuser
ENCODING = 'UTF8'
TABLESPACE = usercenter_sp
LC_COLLATE = 'en_US.UTF-8'
LC_CTYPE = 'en_US.UTF-8'
CONNECTION LIMIT = -1;
#q

psql -h localhost -U postgres -d usercenter< usercenter.sql
(或者用如下命令：
#su postgres
#cd (这里cd后面直接按回车键，表示进入到postgres用户的home目录，linux是多用户的，cd后面不加任何参数表示，进入当前用户的家目录中)
psql usercenter< usercenter.sql
)

c).恢复teamtalk数据库
mkdir -p /var/lib/pgsql/9.4/data/teamtalk_space #创建存放表空间的目录
chown -R postgres:postgres /var/lib/pgsql/9.4/data/teamtalk_space #修改其所属组
#su postgres
#psql
SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
CREATE SCHEMA teamtalk;
ALTER SCHEMA teamtalk OWNER TO immuser;

(1).创建表空间
CREATE TABLESPACE teamtalk_sp
OWNER immuser
LOCATION '/var/lib/pgsql/9.4/data/teamtalk_space';
-- Database: teamtalk;
-- DROP DATABASE teamtalk;

(2).创建数据库，因为备份数据库时只能备份表的结构和内容，所以在回复数据库的时候要先创建数据库，用于恢复数据；
CREATE DATABASE teamtalk
WITH OWNER = immuser
ENCODING = 'UTF8'
TABLESPACE = teamtalk_sp
LC_COLLATE = 'en_US.UTF-8'
LC_CTYPE = 'en_US.UTF-8'
CONNECTION LIMIT = -1;
#q

psql -h localhost -U postgres -d teamtalk< teamtalk.sql
(或者用如下命令：
#su postgres
#cd (这里cd后面直接按回车键，表示进入到postgres用户的home目录，linux是多用户的，cd后面不加任何参数表示，进入当前用户的家目录中)
psql teamtalk< teamtalk_new.sql
)