PostgreSQL/QuickStart/zh-cn

这是一个关于PostgreSQL的快速入门指南. 它涵盖了安装和配置PostgreSQL. 这是对官方文档的补充，但不能代替它.

关于PostgreSQL的一点东西
PostgreSQL 是一个自由和开源的对象-关系型数据库管理系统（RDBMS）. 它支持诸如复杂查询、视图和外键等功能，并且经常被认为更严格地遵守SQL标准，在默认情况下比任何其他数据库（商业或其他）更安全.

请访问postgresql.org上的关于页面了解更多信息.

本文将涉及的内容
这篇文章将指导你通过Gentoo的特定步骤来安装PostgreSQL RDBMS.

本文所涉及的ebuild是.

本文假设你将安装最新的PostgreSQL稳定版本；在撰写本文时，该版本是9.3.5. 根据你的具体版本，对本文中的命令进行必要的调整.

关于Ebuilds
Portage中的PostgreSQL ebuilds具有基于主要版本的solt. 这允许你有两个主要版本的PostgreSQL同时运行；9.0-9.4库和服务器可以同时安装和提供服务. 这在你需要把数据从一个旧的数据库转移到一个新的数据库，或者需要在同一台机器上有一个生产和一个测试数据库的情况下很有用. 除此之外，这可以防止数据库、相应的库或可执行文件被不兼容的更新所覆盖. 这需要进行迁移，本指南在后文会进行描述.

另外，通过次要版本更新提供的错误和安全修复，可以应用而不必担心破坏数据库或已安装的PostgreSQL；9.3.4可以更新到9.3.5，因为它们被保证是兼容的，并且除了安装它和重新启动服务器外，不需要你更多的操作. 迁移、重新配置和初始化都是不必要的.

阅读 PostgreSQL版本政策 以了解更多信息.

本文将涉及的内容
本指南将不涵盖相当多的内容. 官方文档大约有2000多页. 因此，在这个快速入门指南中，很多细节将被遗漏. 将只包括Gentoo的特有问题和一些基本的配置指南.

陈旧的Ebuilds
如果您安装了以下任何一个ebuilds，那么您的Gentoo PostgreSQL安装已经过时了，现在应该迁移.

,, , 和9.0以上的版本.

分离的ebuilds, , 和/或已经统一为一个软件包：. 除了安装统一的ebuild版本之外，你不需要从分裂的ebuild版本转移到统一的ebuild版本

这篇文章涵盖了如何从旧的ebuilds迁移到新的ebuilds.

USE 标志
有关USE标志的信息.
 * doc: 包括在线文档，并存储在你的系统中.
 * pg_legacytimestamp. 使用老式的浮点方法来格式化时间戳，而不是分辨率更高的64位整数方法. 除非你以前的安装使用了这种过时的方法，否则请将这个USE标志关闭. 如果你的数据库使用了时间戳，更改'pg_legacytimestamp'将需要你做一个转储和恢复. 这两种方法是互不兼容的.
 * readline. 你应该启用它. 禁用后会删除psql中的命令行编辑和历史记录.
 * selinux. 这只能通过使用SELinux配置文件来启用.
 * uuid：包括支持生成一个128位的随机唯一标识符. 这对合并数据库很有用，这样重复的几率就变得非常低.

开始安装
您可能会收到关于上述任何软件包被以下任何或全部软件包屏蔽的通知. ,, 或. 这些包都是不维护并被淘汰的. 请参考如何从以前的ebuild迁移到新的ebuild的章节，了解如何处理这种情况.

准备初始化数据库集群
一旦软件包完成，你可能想编辑. 其中有三行影响到服务器的默认值，如果不删除包含数据库集群的目录并重新初始化，以后就不能更改.

PGDATA定义了放置配置文件的位置. DATA_DIR定义了创建数据库集群和相关文件的位置. PG_INITDB_OPTS可以包含任何你想设置的额外选项. 额外的选项是不需要的，因为合理的默认值是，嗯，合理的.

在下面的例子中，PGDATA被设置为配置文件将位于. . DATA_DIR被设置为数据库集群应安装在，这是默认的. 这是默认的. 如果你决定更改默认位置，请记住，在路径中保留主要版本是一个非常好的主意. PG_INITDB_OPTS指出默认的locale应该是en_US.UTF-8. 意味着美式英语排序和格式化，以及UTF-8字符编码.

有六个语言环境选项可以设置并覆盖--locale =. 下表列出了这六个选项，如果使用，格式为：.

因此，如果你希望默认为英语，但你希望信息是瑞典语，那么你的PG_INITDB_OPTS将看起来像这样：

可以在文档中找到服务器支持的完整语言和字符编码列表，但您的系统也支持各种语言和字符编码. 将 的输出与文档中的进行比较.

You can change your locale and encoding selections at database creation time. In order to change the locale for a database after you have created it, you must drop the database and start over again.

This will create the database cluster and store all the related server files into PGDATA and DATA_DIR.

Where the Configuration Files are Located
Sample configuration files can be found in (or whatever version), see the trouble shooting section for the script.

This time the focus is upon the files in the PGDATA directory,, instead with primary focus on the  and  files.

postgresql.conf
This is the main configuration file. The line that you may find of immediate interest is listen_addresses. This variable defines to which addresses PostgreSQL will bind. By default, only localhost and the Unix socket are bound. Changing listen_addresses is not enough to enable remote connections. That will be covered in the next section. The official documentation is fairly easy to understand and is exhaustive on all the settings available. It would behoove you to read that in addition to what is covered here as some things may change.

Of secondary interest is the logging destination. By default, everything is logged to in the DATA_DIR directory. There is an entire subsection of that covers a slew of options for how, what and where to log. The subsection is marked: ERROR REPORTING AND LOGGING.

Other than listen_addresses and the logging options, the rest of the defaults in are reasonable enough to get you going.

pg_hba.conf
The file states who is allowed to connect to the database server and which authentication method must be used to establish the connection. Again, the documentation is quite exhaustive on the settings and what they all mean, but a few things are covered here for clarification.

As has been mentioned before, by default the server is secure. Kind of. There is only one database role that is available for log in by default: postgres. And, the only way to initiate a connection to the database is through the Unix socket, which is owned by the postgres system user and system group, or via localhost. Now for the "kind of" bit: Any user on the system can make a connection to the database through the localhost. Even as the postgres database superuser.

The trust method is what allows any user to log on as any user without a password. It specifies just what it implies: Trust all connections for the given type to the given database from the given database user (but not the system user) from the given location without a password. This is what allows any user on the system to log on as any user through the localhost connection from the get go. This is not as dangerous as it seems, but does pose a serious security risk in most circumstances.

The two methods you will most likely use are: password and md5. The password method only specifies that a password is required to start the connection and the password is sent "in-the-clear". This method is fine when such information will never leave the machine, such as connecting via the Unix socket or localhost. The md5 method is like password, but protects the password by using an md5 hash. This is what you want to use whenever the password is going to traverse a network.

At this point, this author would like to bring your attention to the last two lines, four lines including comments, of the file. PostgreSQL has native support for IPv6 regardless of your desires for such support. Additionally, IPv4 addresses are automatically mapped to IPv6 addresses, i.e., 127.0.0.1 will be mapped to ::FFFF:127.0.0.1 and as "pure" IPv6 ::FFFF:7F00:0001.

There seems to be some misunderstanding, though, as to how host names are mapped to IP addresses. Let us take a look at the file.

From the example above you can see that both an IPv4 and an IPv6 IP address are mapped to localhost. When  refers to this file, it will grab the first match and use that as the address; in this case 127.0.0.1. When PostgreSQL parses this, it will match the IPv6 formatted address as well, e.g. ::ffff:127.0.0.1. If, however, the IPv6 address appears first, then  will map to ::1 alone; ::1 is not the same as ::ffff:127.0.0.1. As such, if you do not have ::1 as a permitted means of access,  will not be able to establish a connection. Furthermore, your kernel needs to support the IPv6 protocol.

So, it is better to specify IP addresses alone to  and in  rather than to rely on  to be ordered properly, and it removes any doubt as to which IP addresses are allowed or to which server you will connect.

Give It a Go!
Now start PostgreSQL and set the password for the database superuser postgres.

Change 'trust' to 'password' for the 'host' (not the 'local', Unix domain socket) connections.

Now start the database:

Open a connection to the server and set the password:

Change 'trust' to 'password' for the local connection:

Now have the database reload the configuration:

Finally, once everything works as it should, have PostgreSQL start at boot:

At this point you are ready to continue on with the official PostgreSQL Tutorial. The tutorial will guide you through creating roles, databases, schemata and all that fun and useful stuff.

When You Need to Migrate
There are only two reasons you would need to perform a migration: When moving from one major version to another, e.g., from PostgreSQL 8.4.7 to 9.0.3, but not from 9.0.2 to 9.0.3; or when switching from the deprecated floating-point timestamp format to the new 64-bit integer timestamp format.

Post-9.0 Migration
When upgrading from a previous version of the recent ebuilds, which is any version after 8.4, follow the beginning of this guide before proceeding with this migration.

pg_upgrade, a new utility that comes along with 9.0 and later, simplifies the migration process rather drastically.

However, there are two caveats with using. Firstly, it does not support configuration files being in a different directory than where the data is stored. This can be resolved by using symbolic links. Lastly, you can only use it to migrate from a database from 8.3 or newer. If you have an older database you will need to follow the instructions to migrate from pre-9.0 deployments.

First ensure that the new database cluster is initialized (as described above). Then stop the servers you're going to migrate from and to:

Check available versions, then select yours:

Change the method of database user 'postgres' to trust on local connections on all databases:

You may need to change the permissions of before you perform the next step.

Perform the tasks  tells you to do, if any.

And now you're done:

Pre-9.0 Migration: With the New Ebuilds
Because the new ebuilds feature a more advanced slotting method than the previous ones, the downtime is quite minimal, most likely minutes rather than hours.

In the following examples, it is assumed that you are using the default locations and port settings, and that you are migrating from 8.3 to 8.4. Adjust accordingly if you have deviated from the default.

If you have not already done so, follow the installation instructions before starting the migration. Such a compile may hamper performance on the database server but it can keep going.

A couple of files need to be tweaked before beginning the migration. Edit PGPORT in the configuration file to 6543. (Any port number other than what your old installation is bound to will do.)

Next, edit so that only the database superuser postgres can access the database cluster via the Unix socket.

The following should be safe. Read the documentation to be sure.

Don't forget to copy over any other configuration files that you may need.

Begin piping the data from the old cluster to the new cluster.

Edit PGPORT back to 5432.

Allow users access once more.

Hopefully everything went according to plan and you have a successfully updated server that contains precisely the same data, bit for bit, as the old server.

Pre-9.0 Migration: From the Obsolete Ebuilds
You will need to schedule some downtime for your server. The old ebuilds cannot be installed at the same time as the new ebuilds. As such, assume that the server will have to be down for a few hours. Maybe for the weekend, even.

Before starting, you will need to deny access to the server, so that no changes are made. You may also want to backup your and  and any other configuration file that you deem important.

Follow the steps detailed in this article for installing and configuring the server.

You may break some packages that were built against those packages, but once you have installed you can run   to reemerge any packages that may have been broken.

pgAdmin
pgAdmin is a graphical utility for managing PostgreSQL.

Server Lacks Instrumentation Functions
This problem is easy to solve, with the solution depending on the version you are using. What is difficult about it is finding the answer. What is required is an import from a file that already exists on the storage drive:. To resolve this issue, run one of the following commands appropriate to the version you have.

For PostgreSQL 9.0 and earlier:

For PostgreSQL 9.1 and later:

Missing config files in and
You can try to move the config files into the directory, where x is your postgresql version number, as follows:

If those files are missing you will need to initialize them.

First su into the postgres user:

Then, as the postgres user, run  and specify the data directory:

This will generate your configuration files and allow you to copy them over to as shown in the first command in this section.

"ERROR: timezone directory stack overflow" or "FATAL: too many private dirs demanded"
This is caused by a symlink loop. To fix it, type the following:

Keep in mind that any update to zoneinfo will recreate this symlink, thus requiring you to remove it again.

Systemd
Force the service to start on bootup:

To start service immediately:

If an error occurs check if the directory is there. If not, then create it like so:

Check the config file permissions:

Service file and changes to it
Systemd service files (postgresql-@SLOT@-.service) can be found in /lib/systemd/system/:

example config change:

This will override the setting appearing in /lib/systemd/system/postgresql-@SLOT@.service.

(source: postgresql-10.service file)

RemoveIPC
PostgreSQL recommends in wiki.postgresql.org/wiki/Systemd to change the RemoveIPC setting to no in /etc/systemd/logind.conf:

(source: wiki.postgresql.org/wiki/Systemd)