Where are the files for a PostgreSQL database stored?
PostgreSQL数据库的文件存储在哪里?
11 个解决方案
#1
To see where the data directory is, use this query.
要查看数据目录的位置,请使用此查询。
show data_directory;
To see all the run-time parameters, use
要查看所有运行时参数,请使用
show all;
You can create tablespaces to store database objects in other parts of the filesystem. To see tablespaces, which might not be in that data directory, use this query.
您可以创建表空间以将数据库对象存储在文件系统的其他部分中。要查看可能不在该数据目录中的表空间,请使用此查询。
SELECT * FROM pg_tablespace;
#2
On Windows7 all the databases are referred by a number in the file named pg_database under C:\Program Files (x86)\PostgreSQL\8.2\data\global. Then you should search for the folder name by that number under C:\Program Files (x86)\PostgreSQL\8.2\data\base. That is the content of the database.
在Windows7上,所有数据库都由C:\ Program Files(x86)\ PostgreSQL \ 8.2 \ data \ global下名为pg_database的文件中的数字引用。然后,您应该在C:\ Program Files(x86)\ PostgreSQL \ 8.2 \ data \ base下搜索该编号的文件夹名称。那是数据库的内容。
#3
Open pgAdmin and go to Properties for specific database. Find OID and then open directory
打开pgAdmin并转到特定数据库的Properties。找到OID然后打开目录
<POSTGRESQL_DIRECTORY>/data/base/<OID>
There should be your DB files.
应该有你的DB文件。
#4
Under my Linux installation, it's here: /var/lib/postgresql/8.x/
在我的Linux安装下,它位于:/var/lib/postgresql/8.x/
You can change it with initdb -D "c:/mydb/"
您可以使用initdb -D“c:/ mydb /”更改它
#5
As suggested in "PostgreSQL database default location on Linux", under Linux you can find out using the following command:
正如“Linux上的PostgreSQL数据库默认位置”所述,在Linux下,您可以使用以下命令找到:
ps aux | grep postgres | grep -- -D
#6
The location of specific tables/indexes can be adjusted by TABLESPACEs:
TABLESPACEs可以调整特定表/索引的位置:
CREATE TABLESPACE dbspace LOCATION '/data/dbs';
CREATE TABLE something (......) TABLESPACE dbspace;
CREATE TABLE otherthing (......) TABLESPACE dbspace;
#7
On Mac: /Library/PostgreSQL/9.0/data/base
在Mac上:/Library/PostgreSQL/9.0/data/base
The directory can't be entered, but you can look at the content via: sudo du -hc data
无法输入目录,但您可以通过以下方式查看内容:sudo du -hc data
#8
Postgres stores data in files in its data directory. Follow the steps below to go to a database and its files:
Postgres将数据存储在其数据目录中的文件中。按照以下步骤转到数据库及其文件:
The database corresponding to a postgresql table file is a directory. The location of the entire data directory can be obtained by running SHOW data_directory. in a UNIX like OS (eg: Mac) /Library/PostgreSQL/9.4/data Go inside the base folder in the data directory which has all the database folders: /Library/PostgreSQL/9.4/data/base
对应于postgresql表文件的数据库是目录。可以通过运行SHOW data_directory获取整个数据目录的位置。在UNIX之类的操作系统(例如:Mac)/Library/PostgreSQL/9.4/data转到数据目录中的基本文件夹,其中包含所有数据库文件夹:/ Library / PostgreSQL / 9.4 / data / base
Find the database folder name by running (Gives an integer. This is the database folder name):
通过运行查找数据库文件夹名称(给出一个整数。这是数据库文件夹名称):
SELECT oid from pg_database WHERE datname = <database_name>;
Find the table file name by running (Gives an integer. This is the file name):
通过运行查找表文件名(给出一个整数。这是文件名):
SELECT relname, relfilenode FROM pg_class WHERE relname = <table_name>;
This is a binary file. File details such as size and creation date time can be obtained as usual. For more info read this SO thread
这是一个二进制文件。可以像往常一样获取文件详细信息,例如大小和创建日期时间。有关更多信息,请阅读此SO线程
#9
I'd bet you're asking this question because you've tried pg_ctl start and received the following error:
我打赌你问这个问题,因为你已经尝试了pg_ctl start并收到以下错误:
pg_ctl: no database directory specified and environment variable PGDATA unset
pg_ctl:未指定数据库目录,并且环境变量PGDATA未设置
In other words, you're looking for the directory to put after -D in your pg_ctl start command.
换句话说,您正在寻找在pg_ctl start命令之后放入-D的目录。
In this case, the directory you're looking for contains these files.
在这种情况下,您要查找的目录包含这些文件。
PG_VERSION pg_dynshmem pg_multixact
pg_snapshots pg_tblspc postgresql.conf
base pg_hba.conf pg_notify
pg_stat pg_twophase postmaster.opts
global pg_ident.conf pg_replslot
pg_stat_tmp pg_xlog postmaster.pid
pg_clog pg_logical pg_serial
pg_subtrans postgresql.auto.conf server.log
You can locate it by locating any of the files and directories above using the search provided with your OS.
您可以使用操作系统提供的搜索找到上面的任何文件和目录,从而找到它。
For example in my case (a HomeBrew install on Mac OS X), these files are located in /usr/local/var/postgres. To start the server I type:
例如,在我的情况下(Mac OS X上的HomeBrew安装),这些文件位于/ usr / local / var / postgres中。要启动服务器,我输入:
pg_ctl -D /usr/local/var/postgres -w start
... and it works.
......它有效。
#10
Everyone already answered but just for the latest updates. If you want to know where all the configuration files reside then run this command in the shell
每个人都已经回答,但只是为了最新的更新。如果您想知道所有配置文件所在的位置,请在shell中运行此命令
SELECT name, setting FROM pg_settings WHERE category = 'File Locations';
#11
On Windows, the PGDATA directory that the PostgresSQL docs describe is at somewhere like C:\Program Files\PostgreSQL\8.1\data. The data for a particular database is under (for example) C:\Program Files\PostgreSQL\8.1\data\base\100929, where I guess 100929 is the database number.
在Windows上,PostgresSQL文档描述的PGDATA目录位于C:\ Program Files \ PostgreSQL \ 8.1 \ data之类的某个位置。特定数据库的数据在(例如)C:\ Program Files \ PostgreSQL \ 8.1 \ data \ base \ 100929下,其中我猜100929是数据库编号。
#1
To see where the data directory is, use this query.
要查看数据目录的位置,请使用此查询。
show data_directory;
To see all the run-time parameters, use
要查看所有运行时参数,请使用
show all;
You can create tablespaces to store database objects in other parts of the filesystem. To see tablespaces, which might not be in that data directory, use this query.
您可以创建表空间以将数据库对象存储在文件系统的其他部分中。要查看可能不在该数据目录中的表空间,请使用此查询。
SELECT * FROM pg_tablespace;
#2
On Windows7 all the databases are referred by a number in the file named pg_database under C:\Program Files (x86)\PostgreSQL\8.2\data\global. Then you should search for the folder name by that number under C:\Program Files (x86)\PostgreSQL\8.2\data\base. That is the content of the database.
在Windows7上,所有数据库都由C:\ Program Files(x86)\ PostgreSQL \ 8.2 \ data \ global下名为pg_database的文件中的数字引用。然后,您应该在C:\ Program Files(x86)\ PostgreSQL \ 8.2 \ data \ base下搜索该编号的文件夹名称。那是数据库的内容。
#3
Open pgAdmin and go to Properties for specific database. Find OID and then open directory
打开pgAdmin并转到特定数据库的Properties。找到OID然后打开目录
<POSTGRESQL_DIRECTORY>/data/base/<OID>
There should be your DB files.
应该有你的DB文件。
#4
Under my Linux installation, it's here: /var/lib/postgresql/8.x/
在我的Linux安装下,它位于:/var/lib/postgresql/8.x/
You can change it with initdb -D "c:/mydb/"
您可以使用initdb -D“c:/ mydb /”更改它
#5
As suggested in "PostgreSQL database default location on Linux", under Linux you can find out using the following command:
正如“Linux上的PostgreSQL数据库默认位置”所述,在Linux下,您可以使用以下命令找到:
ps aux | grep postgres | grep -- -D
#6
The location of specific tables/indexes can be adjusted by TABLESPACEs:
TABLESPACEs可以调整特定表/索引的位置:
CREATE TABLESPACE dbspace LOCATION '/data/dbs';
CREATE TABLE something (......) TABLESPACE dbspace;
CREATE TABLE otherthing (......) TABLESPACE dbspace;
#7
On Mac: /Library/PostgreSQL/9.0/data/base
在Mac上:/Library/PostgreSQL/9.0/data/base
The directory can't be entered, but you can look at the content via: sudo du -hc data
无法输入目录,但您可以通过以下方式查看内容:sudo du -hc data
#8
Postgres stores data in files in its data directory. Follow the steps below to go to a database and its files:
Postgres将数据存储在其数据目录中的文件中。按照以下步骤转到数据库及其文件:
The database corresponding to a postgresql table file is a directory. The location of the entire data directory can be obtained by running SHOW data_directory. in a UNIX like OS (eg: Mac) /Library/PostgreSQL/9.4/data Go inside the base folder in the data directory which has all the database folders: /Library/PostgreSQL/9.4/data/base
对应于postgresql表文件的数据库是目录。可以通过运行SHOW data_directory获取整个数据目录的位置。在UNIX之类的操作系统(例如:Mac)/Library/PostgreSQL/9.4/data转到数据目录中的基本文件夹,其中包含所有数据库文件夹:/ Library / PostgreSQL / 9.4 / data / base
Find the database folder name by running (Gives an integer. This is the database folder name):
通过运行查找数据库文件夹名称(给出一个整数。这是数据库文件夹名称):
SELECT oid from pg_database WHERE datname = <database_name>;
Find the table file name by running (Gives an integer. This is the file name):
通过运行查找表文件名(给出一个整数。这是文件名):
SELECT relname, relfilenode FROM pg_class WHERE relname = <table_name>;
This is a binary file. File details such as size and creation date time can be obtained as usual. For more info read this SO thread
这是一个二进制文件。可以像往常一样获取文件详细信息,例如大小和创建日期时间。有关更多信息,请阅读此SO线程
#9
I'd bet you're asking this question because you've tried pg_ctl start and received the following error:
我打赌你问这个问题,因为你已经尝试了pg_ctl start并收到以下错误:
pg_ctl: no database directory specified and environment variable PGDATA unset
pg_ctl:未指定数据库目录,并且环境变量PGDATA未设置
In other words, you're looking for the directory to put after -D in your pg_ctl start command.
换句话说,您正在寻找在pg_ctl start命令之后放入-D的目录。
In this case, the directory you're looking for contains these files.
在这种情况下,您要查找的目录包含这些文件。
PG_VERSION pg_dynshmem pg_multixact
pg_snapshots pg_tblspc postgresql.conf
base pg_hba.conf pg_notify
pg_stat pg_twophase postmaster.opts
global pg_ident.conf pg_replslot
pg_stat_tmp pg_xlog postmaster.pid
pg_clog pg_logical pg_serial
pg_subtrans postgresql.auto.conf server.log
You can locate it by locating any of the files and directories above using the search provided with your OS.
您可以使用操作系统提供的搜索找到上面的任何文件和目录,从而找到它。
For example in my case (a HomeBrew install on Mac OS X), these files are located in /usr/local/var/postgres. To start the server I type:
例如,在我的情况下(Mac OS X上的HomeBrew安装),这些文件位于/ usr / local / var / postgres中。要启动服务器,我输入:
pg_ctl -D /usr/local/var/postgres -w start
... and it works.
......它有效。
#10
Everyone already answered but just for the latest updates. If you want to know where all the configuration files reside then run this command in the shell
每个人都已经回答,但只是为了最新的更新。如果您想知道所有配置文件所在的位置,请在shell中运行此命令
SELECT name, setting FROM pg_settings WHERE category = 'File Locations';
#11
On Windows, the PGDATA directory that the PostgresSQL docs describe is at somewhere like C:\Program Files\PostgreSQL\8.1\data. The data for a particular database is under (for example) C:\Program Files\PostgreSQL\8.1\data\base\100929, where I guess 100929 is the database number.
在Windows上,PostgresSQL文档描述的PGDATA目录位于C:\ Program Files \ PostgreSQL \ 8.1 \ data之类的某个位置。特定数据库的数据在(例如)C:\ Program Files \ PostgreSQL \ 8.1 \ data \ base \ 100929下,其中我猜100929是数据库编号。