PostgreSQL 常用备忘

最新推荐文章于 2025-05-23 12:42:14 发布

原创最新推荐文章于 2025-05-23 12:42:14 发布 · 1.8k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#postgresql #archive #command #服务器 #windows #immutable

数据库专栏收录该内容

3 篇文章

订阅专栏

本文介绍了PostgreSQL数据库的实用技巧，包括如何导出建表语句、创建热备份、中文排序、时间字段赋值等。此外，还提供了Vista系统下安装PostgreSQL的方法及SQL查询示例。

1.如何把开始的建表语句给导出来?

a.导出一个 pg_dump -d dbname -t tablename -s > sql.txt

b.全部一起出来pg_dump -d dbname -s > sql.txt

可以指定 function,view,trigger,type

2.热备份

pg_standby 支持创建“热备份”的数据库服务器。它被设计成是可用于生产环境的程序，并且还包括可以客户化的模版，这样用户可以自己进行定制修改。

要用 pg_standby 配置一个备份服务器，把这些放到它的 recovery.conf 配置文件中：

 restore_command = 'pg_standby archiveDir %f %p %r'

这里的 archiveDir 是恢复需要的 WAL 段文件所在的目录。

pg_standby 的命令行的完整的语法是：

 pg_standby [ option ... ] archivelocation nextwalfile xlogfilepath [ restartwalfile ]

当运行在 restore_command 里面时，应该分别为 nextwalfile 和 xlogfilepath 分别声明 %f 和 %p 红，以提供恢复所需要的实际文件和路径。

如果声明了 restartwalfile，通常是使用 %r 宏，那么所有逻辑上在这个文件之前的文件将被从 archivelocation 中删除。这样就令需要保存的文件数目最小化，而同时还保存崩溃重启的能力。如果 archivelocation 是一个用于这个备份服务器的临时的阶段性区域，那么使用这个参数就很合适，但如果 archivelocation 的目录是长期的 WAL 归档区域，那就不合适。

pg_standby 假设 archivelocation 是一个拥有服务器的用户可读的目录。如果声明了 restartwalfile（或 -k），那么 archivelocation 还必须是可写的。

**表 F-26. pg_standby 选项**
选项	缺省	描述
-c	是	使用 cp 或者 copy 命令从归档中恢复 WAL 文件。
-d	否	在 stderr 上打印大量调试输出。
-k numfiles	0	从 archivelocation 删除文件，这样在归档中保存的当前 WAL 文件之前的数不超过指定数目。零（缺省）意思是不从 archivelocation 中删除任何文件。如果声明了 restartwalfile，那么会悄然丢弃这个参数，因为 restartwalfile 声明的归档分离位置更加准确。这个参数在 PostgreSQL 8.3 中已经废弃了；声明 restartwalfile 参数更安全也更准取。一个太小的设置会导致删除了重启备份服务器仍然需要的文件，而太大的设置又会浪费归档空间。
-l	no	使用 ln 命令从归档中恢复 WAL 文件。链接比拷贝效率高很多，不过缺省是拷贝，因为链接不能在所有场合都可用。在 Windows 里头，这个选项使用 mklink 命令提供一个文件到文件的符号链接。-l 在 Windows Vista 之前的 Windows 版本不可用。
-r maxretries	3	设置在拷贝或者链接失败的时候重试的最大次数。每次出错之后，我们都等待 sleeptime * num_retries 秒，这样等待时间就是逐步增加的。因此，缺省的时候，我们会在给备份服务器报告错误之前等待 5 秒，10 秒，然后 15 秒。这个信息可以理解为恢复的完成，也即备份服务器完全启动完毕。
-s sleeptime	5	设置两次检查在归档中是否有可用于恢复的 WAL 之间的睡眠秒数（最多 60）。我们不推荐使用缺省设置；参阅24.4节的讨论。
-t triggerfile	none	声明一个触发器文件，如果出现这个触发器文件，恢复就将终止，不管下一个 WAL 文件是否可用。我们建议你构造一个避免在同个系统存在多个服务器的时会混淆的 filename；比如 /tmp/pgsql.trigger.5432。
-w maxwaittime	0	设置等待下一个 WAL 文件的最大秒数，在这么多秒之后，恢复将停止，备份数据库将启动。设置为零意味着永远等待。我们并不建议一定要用缺省；参阅 24.4 节的讨论。

注意: 不支持 --help，因为除了在开发和测试之外，pg_standby 的目的不是给交互使用的。

例子

在 Linux 或者 Unix 系统里，你可以使用：

archive_command = 'cp %p .../archive/%f'

restore_command = 'pg_standby -l -d -s 2 -t /tmp/pgsql.trigger.5442 .../archive %f %p %r 2>>standby.log'

这里的 archive 目录物理上位于备份服务器上，因此 archive_command 是通过 NFS 访问之的，但是文件对备份服务器而言是本地的（允许使用 ln）。这样就可以：

使用 ln 命令从归档中恢复 WAL 文件
在 standby.log 中生成调试输出
在两次检查 WAL 文件的可用性之间睡眠两秒
只有在一个叫 /tmp/pgsql.trigger5442 的触发器文件出现的时候才停止等待
从归档目录中删除不再需要的文件

在 Windows 上，你可以用：

archive_command = 'copy %p ...//archive//%f'

restore_command = 'pg_standby -d -s 5 -t C:/pgsql.trigger.5442 .../archive %f %p %r 2>>standby.log'

请注意在 archive_command 里面，反斜杠需要写双份，而 restore_command 里面不需要。这样写就会：

使用 copy 命令从归档中恢复 WAL 文件
在 standby.log 中产生调试输出
两次检查是否有可用的 WAL 文件之间睡眠 5 秒
只有在一个叫 C:/pgsql.trigger.5442 的触发器文件出现的时候才停止等待
从 archive 目录里删除不再需要的文件

因为 Windows 例子在两边都使用了 copy，所以两个服务器都可以透过网络访问 archive 目录。

3.如何让数据按照中文排序

PG提供了很好的编码转换函数：convert()，使用这个函数，我们可以有效地把UTF8编码转换成GBK编码。比如，要把UTF8编码转换成GBK编码：

  select convert('UTF8编码字串或字段' using utf8_to_gbk);

这样就可以了，那么，是不是我们可以这样实现GBK的读音序编码呢：

  select * from table order by convert( column_need_to_sort_in_utf8 using utf8_to_gbk);

还不行

因为没有内置的TEXT到BYTEA的转换函数，我们需要自己做一个：
create or replace function text2bytea(text) returns bytea as
$$
return $1;
$$ language plpgsql immutable;

这个函数很简单，利用了PG提供的text_in()和bytea_out的输入输出转换函数，直接生成BYTEA的数据。

于是，我们可以这样进行中文的语音排序了：

  select * from table order by text2bytea(convert(column_need_to_sort_in_utf8 using utf8_to_gbk));
4.怎么给时间日期字段赋值成当前日期时间？
CREATE TABLE test (x int, modtime TIMESTAMP DEFAULT CURRENT_TIMESTAMP );
5.vista如何安装postgreSQL?
安装前先把"Secondary Logon"这个服务打开

6.如何只选择一个查询结果随机的一行

   SELECT  cols
      FROM tab
      ORDER BY random()
      LIMIT 1 ;
7.如何更改一个字段的数据类型？
ALTER TABLE ALTER COLUMN TYPE

8.我怎样检测一个字段是否为 NULL

用 IS NULL 和 IS NOT NULL 测试这个字段，具体方法如下： 
  SELECT *
  FROM tab
  WHERE col IS NULL;