pgsql操作数据库命令

2019-11-08 20:55:54

字体：大中小

来源：转载

供稿：网友

关于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;#/qpsql -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;#/qpsql -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#psqlSET 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)

上一篇：给SQL查询结果加上序号

下一篇：Tips: 快速查询定位最慢的SQL