Linux 上的 PostgreSQL 数据库默认位置

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/3004523/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-08-03 20:07:14  来源:igfitidea点击:

PostgreSQL database default location on Linux

linuxpostgresql

提问by gruszczy

What is the default directory where PostgreSQL will keep all databases on Linux?

PostgreSQL 将在 Linux 上保存所有数据库的默认目录是什么?

采纳答案by leonbloy

The "directory where postgresql will keep all databases" (and configuration) is called "data directory" and corresponds to what PostgreSQL calls (a little confusingly) a "database cluster", which is not related to distributed computing, it just means a group of databases and related objects managed by a PostgreSQL server.

postgresql 将保存所有数据库的目录”(和配置)称为“数据目录”,对应于 PostgreSQL 称为(有点混乱)的“数据库集群”,与分布式计算无关,仅表示一组由 PostgreSQL 服务器管理的数据库和相关对象。

The location of the data directory depends on the distribution. If you install from source, the default is /usr/local/pgsql/data:

数据目录的位置取决于分布。如果从源代码安装,默认为/usr/local/pgsql/data

In file system terms, a database cluster will be a single directory under which all data will be stored. We call this the data directory or data area. It is completely up to you where you choose to store your data. There is no default, although locations such as /usr/local/pgsql/data or /var/lib/pgsql/data are popular. (ref)

在文件系统术语中,数据库集群将是一个目录,所有数据都将存储在该目录下。我们称之为数据目录或数据区。选择存储数据的位置完全取决于您。虽然 /usr/local/pgsql/data 或 /var/lib/pgsql/data 等位置很受欢迎,但没有默认值。(参考

Besides, an instance of a running PostgreSQL server is associated to one cluster; the location of its data directory can be passed to the server daemon ("postmaster" or "postgres") in the -Dcommand line option, or by the PGDATAenvironment variable (usually in the scope of the running user, typically postgres). You can usually see the running server with something like this:

此外,一个正在运行的 PostgreSQL 服务器实例与一个集群相关联;其数据目录的位置可以通过命令行选项或通过环境变量(通常在运行用户的范围内,通常)传递给服务器守护程序(“ postmaster”或“ postgres” )。您通常可以看到正在运行的服务器,如下所示:-DPGDATApostgres

[root@server1 ~]# ps auxw |  grep postgres | grep -- -D
postgres  1535  0.0  0.1  39768  1584 ?        S    May17   0:23 /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data

Note that it is possible, though not very frequent, to run two instances of the same PostgreSQL server (same binaries, different processes) that serve different "clusters" (data directories). Of course, each instance would listen on its own TCP/IP port.

请注意,虽然不是很频繁,但可以运行同一 PostgreSQL 服务器的两个实例(相同的二进制文件,不同的进程),为不同的“集群”(数据目录)提供服务。当然,每个实例都会监听自己的 TCP/IP 端口。

回答by silent

/var/lib/postgresql/[version]/data/

At least in Gentoo Linux and Ubuntu 14.04 by default.

至少在默认情况下在 Gentoo Linux 和 Ubuntu 14.04 中是这样。

You can find postgresql.confand look at param data_directory. If it is commented then database directory is the same as this config file directory.

您可以找到postgresql.conf并查看 param data_directory。如果有注释,则数据库目录与此配置文件目录相同。

回答by Dum

On Centos 6.5/PostgreSQL 9.3:

在 Centos 6.5/PostgreSQL 9.3 上:

Change the value of "PGDATA=/var/lib/pgsql/data"to whatever location you want in the initial script file /etc/init.d/postgresql.

将 的值更改为"PGDATA=/var/lib/pgsql/data"您想要在初始脚本文件中的任何位置/etc/init.d/postgresql

Remember to chmod 700and chown postgres:postgresto the new location and you're the boss.

请记住, chmod 700chown postgres:postgres在新的位置,你是老板。

回答by Léo Léopold Hertz ??

Default in Debian 8.1 and PostgreSQL 9.4 after the installation with the package manager apt-get

使用包管理器apt-get安装后,Debian 8.1 和 PostgreSQL 9.4 中的默认值

ps auxw |  grep postgres | grep -- -D
postgres 17340  0.0  0.5 226700 21756 ?        S    09:50   0:00 /usr/lib/postgresql/9.4/bin/postgres -D /var/lib/postgresql/9.4/main -c config_file=/etc/postgresql/9.4/main/postgresql.conf

so apparently /var/lib/postgresql/9.4/main.

很明显/var/lib/postgresql/9.4/main

回答by null

I think best method is to query pg_settingview:

我认为最好的方法是查询pg_setting视图:

 select s.name, s.setting, s.short_desc from pg_settings s where s.name='data_directory';

Output:

输出:

      name      |        setting         |            short_desc
----------------+------------------------+-----------------------------------
 data_directory | /var/lib/pgsql/10/data | Sets the server's data directory.
(1 row)

回答by k_o_

The command pg_lsclusters(at least under Linux / Ubuntu) can be used to list the existing clusters and with it also the data directory:

该命令pg_lsclusters(至少在 Linux / Ubuntu 下)可用于列出现有集群以及数据目录:

Ver Cluster Port Status Owner    Data directory               Log file
9.5 main    5433 down   postgres /var/lib/postgresql/9.5/main /var/log/postgresql/postgresql-9.5-main.log
10  main    5432 down   postgres /var/lib/postgresql/10/main  /var/log/postgresql/postgresql-10-main.log

回答by Srikant Patra

Below query will help to find postgres configuration file.

下面的查询将有助于找到 postgres 配置文件。

postgres=# SHOW config_file;
             config_file
-------------------------------------
 /var/lib/pgsql/data/postgresql.conf
(1 row)

[root@node1 usr]# cd /var/lib/pgsql/data/
[root@node1 data]# ls -lrth
total 48K
-rw------- 1 postgres postgres    4 Nov 25 13:58 PG_VERSION
drwx------ 2 postgres postgres    6 Nov 25 13:58 pg_twophase
drwx------ 2 postgres postgres    6 Nov 25 13:58 pg_tblspc
drwx------ 2 postgres postgres    6 Nov 25 13:58 pg_snapshots
drwx------ 2 postgres postgres    6 Nov 25 13:58 pg_serial
drwx------ 4 postgres postgres   36 Nov 25 13:58 pg_multixact
-rw------- 1 postgres postgres  20K Nov 25 13:58 postgresql.conf
-rw------- 1 postgres postgres 1.6K Nov 25 13:58 pg_ident.conf
-rw------- 1 postgres postgres 4.2K Nov 25 13:58 pg_hba.conf
drwx------ 3 postgres postgres   60 Nov 25 13:58 pg_xlog
drwx------ 2 postgres postgres   18 Nov 25 13:58 pg_subtrans
drwx------ 2 postgres postgres   18 Nov 25 13:58 pg_clog
drwx------ 5 postgres postgres   41 Nov 25 13:58 base
-rw------- 1 postgres postgres   92 Nov 25 14:00 postmaster.pid
drwx------ 2 postgres postgres   18 Nov 25 14:00 pg_notify
-rw------- 1 postgres postgres   57 Nov 25 14:00 postmaster.opts
drwx------ 2 postgres postgres   32 Nov 25 14:00 pg_log
drwx------ 2 postgres postgres 4.0K Nov 25 14:00 global
drwx------ 2 postgres postgres   25 Nov 25 14:20 pg_stat_tmp