postgres日常维护

postgres日常维护

一、postgres
(一)、登陆生产库
1、生产库（未启用，导入外部数据）：
su - postgres
cd /data/pgsql/
./bin/psql -p 15432 -U postgres -W
根据提示输入首次登录设置的登录密码：密码

/data/pgsql/bin/psql -U 用户名 -h ..**.*5 -p 5432 -W
数据密码：密码passwd

2、腾讯云(神策)
/data/pgsql/bin/pg_dump -U 用户名 -h ..**.*3 -p 5432 -W
数据密码：密码passwd
(二)、备份还原数据库
1、备份：
使用 PostgreSQL 客户端连接本地数据库，执行如下命令，备份数据。
pg_dump -U username -h hostname -p port -d databasename -f filename
参数 说明
username 本地数据库用户名
hostname 本地数据库主机名，如果是在本地数据库主机登录，可以使用 localhost
port 本地数据库端口号
databasename 要备份的本地数据库名
filename 要生成的备份文件名称，如 mydump.sql

/data/pgsql/bin/pg_dump -U 用户名 -h ..**.*5 -p 5432 -d 数据库名 -f /home/bak/数据库名.sql

2、还原
准备操作：请先将备份好的数据上传到同一内网的云服务器主机上，然后通过内网进行数据恢复，以保证网络的稳定和数据的安全。
在您登录云服务器后，在 PostgreSQL 客户端中，执行如下命令，恢复数据。
psql -U username -h hostname -d desintationdb -p port -f dumpfilename

/data/pgsql/bin/psql -h127.0.0.1 -p 15432-U biuser
参数 说明
username TencentDB for PostgreSQL 的数据库用户名
hostname TencentDB for PostgreSQL 的数据库地址
desintationdb TencentDB for PostgreSQL 的数据库名
port TencentDB for PostgreSQL 的数据库端口号
dumpfilename 备份数据文件名，如 mydump.sql

3、建库、建用户

赋予所有表的所有权限给指定用户

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO “用户名”;

赋予指定表的所有权限给指定用户

GRANT ALL PRIVILEGES ON “表名” TO “用户名”;
GRANT ALL PRIVILEGES ON 数据库名.* to “biuser”;

#修改库的所有者
alter database 库名 owner to 用户名;
alter database 数据库名 owner to biuser;

#授予用户库权限
grant ALL ON DATABASE 库名 TO 用户名;
grant ALL ON DATABASE 数据库名 TO biuser;

#授予用户指定的库权限
grant select on all tables in schema public to 用户名; // 在那个db执行就授哪个db的权限

#修改表的所有者
alter table 表名 owner to 用户名;
#授予用户表权限
GRANT ALL ON 表名 TO 用户名;
GRANT ALL ON 数据库名.* TObiuser;

#修改sequence所有者
alter sequence 序列名 owner to 用户名;
#修改sequence权限
GRANT ALL ON 序列名 TO 用户名;
4、实战还原腾讯pg
（1）备份腾讯上神策（开通vpn）
测试连通，要加数据库
/data/pgsql/bin/psql -h ..**.*3 -p 5432 -U 用户名 -d 数据库名

导出
/data/pgsql/bin/pg_dump -h ..**.*3 -p 5432 -U 用户名 -d 数据库名> /data/pgbak/gjh.sql
数据密码：密码passwd
(2)、本地还原

建立对应数据和用户
cd /data/pgsql/
./bin/psql -p 15432 -U postgres -W
CREATE USER biuser; #创建用户
CREATE DATABASE 数据库名 OWNER biuser; #创建DATABASE用户
ALTER USER biuser WITH PASSWORD ‘密码’; #设置用户的密码
\q #退出数据库

导入数据到本地数据库
/data/pgsql/bin/psql -h127.0.0.1 -p 15432-U postgres-d 数据库名-f /data/pgbak/gjh.sql
密码：密码

(三)、常用命令
1、连接数据库,
默认的用户和数据库是postgres
psql -U user -d dbname

2、切换数据库
相当于mysql的use dbname
\c dbname

3、列举数据库
相当于mysql的show databases
\l

4、列举表
相当于mysql的show tables
\dt

5、查看表结构
相当于desc tblname,show columns from tbname
\d tblname
\di 查看索引

6、创建数据库：
create database [数据库名];

7、删除数据库
drop database [数据库名];

8、*重命名一个表：
alter table [表名A] rename to [表名B];
9、*删除一个表：
drop table [表名];
10、*在已有的表里添加字段：
alter table [表名] add column [字段名] [类型];
11、*删除表中的字段：
alter table [表名] drop column [字段名];
12、重命名一个字段：
alter table [表名] rename column [字段名A] to [字段名B];
13给一个字段设置缺省值：
alter table [表名] alter column [字段名] set default [新的默认值];
14、*去除缺省值：
alter table [表名] alter column [字段名] drop default;
15、在表中插入数据：
insert into 表名 ([字段名m],[字段名n],…) values ([列m的值],[列n的值],…);
16、修改表中的某行某列的数据：
update [表名] set [目标字段名]=[目标值] where [该行特征];
17、删除表中某行数据：
delete from [表名] where [该行特征];
delete from [表名];–删空整个表
18、创建表：
create table ([字段名1] [类型1] <references 关联表名(关联的字段名)>;,[字段名2] [类型2],…<,primary key (字段名m,字段名n,…)>;);
19、其他
\copyright 显示 PostgreSQL 的使用和发行条款
\encoding [字元编码名称]
显示或设定用户端字元编码
\h [名称] SQL 命令语法上的说明，用 * 显示全部命令
\prompt [文本] 名称
提示用户设定内部变数
\password [USERNAME]
securely change the password for a user
\q 退出psql

可以使用pg_dump和pg_dumpall来完成。比如备份sales数据库：
pg_dumpdrupal>/opt/Postgresql/backup/1.bak