从原始物理文件中恢复postgreSQL数据库[英] Recover postgreSQL databases from raw physical files

本文是小编为大家收集整理的关于从原始物理文件中恢复postgreSQL数据库的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

我有以下问题,我需要知道是否有一种修复方法.

我有一个廉价的客户,足以拒绝购买他公司的主系统上的PostgreSQL数据库,并正如我认为有一天会发生的那样,某些OS文件在停电过程中崩溃了,操作系统需求需求重新安装.

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

我的问题是:有什么方法可以从数据文件夹中恢复数据库?我在Windows环境(XP Service Pack 2)中使用PostgreSQL 8.2工作,需要重新安装PostgreSQL,将其重新安装在新服务器中.我需要在新环境中重新创建数据库,并以某种方式将旧文件附加到新的数据库实例.我知道在SQL Server中这是可能的,因为引擎存储数据库的方式,但我在Postgres中没有任何线索.

有什么想法吗?他们将不胜感激.

推荐答案

如果您拥有整个数据文件夹,则拥有所需的一切(只要架构是相同的).只需尝试在删除这台机器之前在另一台计算机上恢复它,以防万一您没有复制某些内容.

只需将数据目录保存到磁盘即可.启动Postgres时,设置参数,告诉它数据目录在哪里(请参阅: >).或删除新鲜安装的原始数据目录并将副本放在其位置.

其他推荐答案

这是可能的,您只需要将"数据"文件夹(在Postgres安装文件夹内)从旧计算机复制到新计算机,但是有几件事要牢记.

首先,在复制文件之前,必须停止Postgres服务器服务.因此,控制面板 - >管理工具 - >服务,找到Postgres服务并停止它.复制文件并设置权限后,重新开始.

第二,您需要设置数据文件的权限.因为Postgres Server实际上在另一个用户帐户上运行,因此如果您只将文件复制到数据文件夹中,它将无法访问该文件,因为它将没有权限.因此,您需要将文件的所有权更改为" Postgres"用户.我不得不使用

(也应该从资源管理器中进行更改所有权:首先,您必须在文件夹选项中禁用"使用简单的文件共享",然后将出现"安全"选项卡中的"安全性"选项卡,并且在"文件夹属性"对话框中,并且有一个选项到其中to设置权限和更改所有权,但我无法那样做.)

现在,如果服务器服务在您再次手动启动后无法启动,则通常可以在事件查看器(管理工具 - >事件查看器)中查看原因. Postgres会抛出一个错误事件,检查它将为您提供有关问题的线索(有时它会抱怨邮政局长.文件,只需将其删除等).

其他推荐答案

这个问题很旧,但是我想分享我发现的有效方法.

如果您没有带有" PG_DUMP"的备份,并且您的旧数据是文件夹,请尝试以下步骤. 在Postgres数据库中,将记录添加到" PG_DATABASE"表中.使用经理程序或"插入". 进行必要的检查并更改以下插入查询并运行它.

查询工作后将返回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';

本文地址:https://www.itbaoku.cn/post/597547.html

问题描述

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.

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.

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.

Any ideas? They would be much appreciated.

推荐答案

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.

其他推荐答案

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.

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.

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 subinacl for this, install it first, and then use it from command prompt like this (first navigate to folder where you installed it):

subinacl /subdirectories "C:\Program Files\PostgreSQL\8.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.).

其他推荐答案

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.

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.


    /*
    ------------------------------------------
    *** 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';