Postgresql运维常用技巧

在Mysql被收购后,Postgresql就被拿来替代使用了。作为开源和学究气息非常浓的数据库软件,在中文技术维护方面的资料还是有点难找。

1.1操作备份与恢复

操作表之前备份表是一个好习惯

CREATE TABLE 目标表_时间戳_操作原因 AS SELECT * FROM 备份表

1.2恢复数据

`Truncate TABLE tablename;

INSERT INTO 目标表 SELECT * FROM 备份表; `

1.3导入指定字段

INSERT INTO 目标表 (字段1, 字段2, …) SELECT 字段1, 字段2, …FROM 备份表;

2.psql语句返回值意义

如果psql正常完成,它会向 shell 返回 0。如果它自身发生一个致命错误(例如内存用完、找不到文件),它会返回 1。如果到服务器的连接出问题并且事务不是交互式的,它会返回2。如果在脚本中发生错误,它会返回 3 并且变量ON_ERROR_STOP会被设置。

3.不让输出文字自动换行

有时候自动换行会让信息看不清楚,这时候修改下输出信息格式,pset参数设置和PG版本有关。

\pset columns 10000

pset [ option [ value ] ]

border 0(没有边框)、1(内部分隔线)和 2(表格边框)有columns 为wrapped格式设置目标宽度

4.导入csv等文件必须确保文件格式匹配,必须为UTF8,否则无法导入会返回错误信息。一般从windows下面的文件换行等都有特殊的格式无法直接导入。

5.PG的类似upsert功能,当你插入数据碰到已经存在相关条目时,可以选择相关操作,Do nothing 或者 相关操作。

`INSERTINTOtable_name(column_list)VALUES

ONCONFLICTtarget action;

e.g

INSERTINTOcustomers (name, email)

VALUES

(

‘Microsoft’,

‘hotline@microsoft.com’

)

ONCONFLICT(name)

DO

UPDATE

SETemail =EXCLUDED.email || ‘;’ || customers.email;`

6.比较两表数据区别

在foo表里但不在bar里面

`SELECT ID, NAME, ‘not in bar’ AS note FROM foo EXCEPT

SELECT ID, NAME, ‘not in bar’ AS note FROM bar`

或者文件比较少的时候,可以导出成文件,然后使用linux命令grep -v -f bar.table foo.table

7.拷贝数据到文件,可以使用内置的copy命令:

COPY persons TO ‘/tmp/persons.csv’WITHDELIMITER‘,’NULLAS ‘’ ;

也可以选择几列,例子:

COPY persons (first\_name,last\_name,email) TO ’C:\\tmp\\persons\_partial\_db.csv’ DELIMITER ‘,’ CSVHEADER;

也可以条件选择相关数据copy selected data to csv,可以用内部copy命令,例子:

\copy (SELECT * FROM persons) to ‘C:\\tmp\\persons\_client.csv’ with csv

8.pg输出列太长的会自动换行,可以用x,竖直显示数据条目。

9.结束pg里的卡住的进程,要使用pg_stat_activity字段,PG版本要大于9

select pg_terminate_backend(pid)只能杀单个进程,当要杀多个时候,可以用下列命令

select pg_terminate_backend(pid)就可以 from pg_stat_activity where state <> 'idle' and now() - pg_stat_activity.query_start > interval '50 minutes' and datname='数据库名';

我来评几句
登录后评论

已发表评论数()

相关站点

热门文章