database 从原始物理文件中恢复 postgreSQL 数据库

Recover postgreSQL databases from raw physical files

提问by Alvos

I have the following problem and I need to know if there′s a way to fix it.

我有以下问题，我需要知道是否有办法解决它。

I have a client who was cheap enough to decline buying a backup plan for his postgreSQL databases on the main system that runs his company and as I thought it would happen some day, some OS files crashed during a blackout and the OS needs to be reinstalled.

我有一个客户，他很便宜，拒绝在运行他公司的主系统上为他的 postgreSQL 数据库购买备份计划，正如我认为有一天会发生的那样，一些操作系统文件在停电期间崩溃，需要重新安装操作系统.

This client didn't have any backups of the databases but I managed to save the PostgreSQL main directory. I read that the databases are stored somehow inside the data directory of the postgres main folder.

该客户端没有任何数据库备份，但我设法保存了 PostgreSQL 主目录。我读到数据库以某种方式存储在 postgres 主文件夹的数据目录中。

My question is: Is there any way to recover the databases from the data folder only? I am working in a windows environment (XP service pack 2) with PostgreSQL 8.2 and I need to reinstall PostgreSQL in a new server. I would need to recreate the databases in the new environment and somehow attach the old files to the new database instances. I know that's possible in SQL Server because of the way that engine stores the databases but I have no clue in postgres.

我的问题是：有没有办法只从数据文件夹中恢复数据库？我在带有 PostgreSQL 8.2 的 Windows 环境（XP Service Pack 2）中工作，我需要在新服务器中重新安装 PostgreSQL。我需要在新环境中重新创建数据库，并以某种方式将旧文件附加到新数据库实例。我知道这在 SQL Server 中是可能的，因为引擎存储数据库的方式，但我对 postgres 一无所知。

Any ideas? They would be much appreciated.

有任何想法吗？他们将不胜感激。

采纳答案by Konrad Garus

If you have the whole data folder, you have everything you need (as long as architecture is the same). Just try restoring it on another machine before wiping this one out, in case you didn't copy something.

如果您拥有整个数据文件夹，那么您就拥有了所需的一切（只要架构相同）。只需尝试在另一台机器上恢复它，然后再擦除它，以防您没有复制某些东西。

Just save the data directory to disk. When launching Postgres, set the parameter telling it where the data directory is (see: wiki.postgresql.org). Or remove original data directory of the fresh installation and place the copy in its place.

只需将数据目录保存到磁盘。启动 Postgres 时，设置参数告诉它数据目录在哪里（参见：wiki.postgresql.org）。或者删除全新安装的原始数据目录并将副本放在其位置。

回答by kolufild

This is possible, you just need to copy the "data" folder (inside the Postgres installation folder) from the old computer to the new one, but there are a few things to keep in mind.

这是可能的，您只需要将“data”文件夹（在 Postgres 安装文件夹内）从旧计算机复制到新计算机，但要记住一些事情。

First, before you copy the files, you must stop the Postgres server service. So, Control Panel->Administrative tools->Services, find Postgres service and stop it. When you're done copying the files and setting permissions, start it again.

首先，在复制文件之前，您必须停止 Postgres 服务器服务。所以，控制面板->管理工具->服务，找到Postgres服务并停止它。完成复制文件和设置权限后，再次启动它。

Second, you need to set the permissions for the data files. Because postgres server actually runs on another user account, it will not be able to access the files if you just copy them into the data folder, because it will not have permissions to do so. So you need to change the ownership of the files to the "postgres" user. I had to use subinaclfor this, install it first, and then use it from command prompt like this (first navigate to folder where you installed it):

其次，您需要设置数据文件的权限。因为 postgres 服务器实际上运行在另一个用户帐户上，如果您只是将它们复制到数据文件夹中，它将无法访问这些文件，因为它没有权限这样做。因此，您需要将文件的所有权更改为“postgres”用户。我必须为此使用subinacl，先安装它，然后像这样从命令提示符使用它（首先导航到安装它的文件夹）：

subinacl /subdirectories "C:\Program Files\PostgreSQL.2\data\*" /setowner=postgres

(Changing ownership should also be possible to do from the explorer: first you must disable "Use simple file sharing" in Folder options, then a "Security" tab will appear in the folder Properties dialog, and there are options there to set permissions and change ownership, but I wasn't able to do it that way.)

（也应该可以从资源管理器中更改所有权：首先您必须在文件夹选项中禁用“使用简单文件共享”，然后文件夹属性对话框中将出现一个“安全”选项卡，那里有设置权限和更改所有权，但我无法那样做。）

Now, if the server service can't start after you start it manually again, you can usually see the reason in the Event viewer (Administrative tools->Event viewer). Postgres will throw an error event, and inspecting it will give you a clue about what the problem is (sometimes it will complain about a postmaster.pid file, just remove it, etc.).

现在，如果服务器服务在您再次手动启动后无法启动，您通常可以在事件查看器（管理工具->事件查看器）中看到原因。Postgres 会抛出一个错误事件，检查它会给你一个关于问题是什么的线索（有时它会抱怨 postmaster.pid 文件，只需删除它等）。

回答by user2615084

I do so but the most tricky part was to change the owner permission:

我这样做了，但最棘手的部分是更改所有者权限：

1. go to services from administative tools
2. find postgres service and double click on it
3. at log on tab change to local system
4. then restart

1. 从管理工具转到服务
2. 找到 postgres 服务并双击它
3. 在登录选项卡更改为本地系统
4. 然后重新启动

回答by Zeki Gürsoy

The question is very old, but I want to share an effective method that I found.

这个问题很老了，但我想分享一个我发现的有效方法。

If you have not got a backup with "pg_dump" and your old data is folder, try the following steps. In the Postgres database, add records to the "pg_database" table. With a manager program or "insert into". Make the necessary check and change the following insert query and run it.

如果您没有使用“pg_dump”进行备份并且您的旧数据是文件夹，请尝试以下步骤。在 Postgres 数据库中，将记录添加到“pg_database”表中。用管理器程序或“插入”。进行必要的检查并更改以下插入查询并运行它。

The query will return an OID after it has worked. Create a folder with the name of this number. Once you have copied your old data into this folder, the use is now ready.

查询成功后将返回一个 OID。使用此号码的名称创建一个文件夹。将旧数据复制到此文件夹后，现在就可以使用了。

    /*
    ------------------------------------------
    *** Recover From Folder ***
    ------------------------------------------
    Check this table on your own system.
    Change the differences below.
    */
    INSERT INTO
      pg_catalog.pg_database(
      datname, datdba, encoding, datcollate, datctype, datistemplate, datallowconn,
      datconnlimit, datlastsysoid, datfrozenxid, datminmxid, dattablespace, datacl)
    VALUES(
                             -- Write Your collation  
      'NewDBname', 10, 6, 'Turkish_Turkey.1254', 'Turkish_Turkey.1254',
      False, True, -1, 12400, '536', '1', 1663, Null);

    /*
    Create a folder in the Data directory under the name below New OID.
    All old backup files in the directory "data\base\Old OID" are the directory with the new OID number
    Copy. The database is now ready for use.
    */
    select oid from pg_database a where a.datname = 'NewDBname';