是否可以通过简单地交换一些文件来恢复Postgres数据库的速度? [英] Is it possible to restore a Postgres database by simply swapping out some files for speed?

问题描述

这是一个一般的Postgres备份和还原方法问题，基于以下非生产服务器(即本地测试服务器)的用例.

This is a general Postgres backup and restore method question, based on the following use case for a non production server (i.e. a local testing server).

我有一个约20gb的数据库，在测试php脚本时会对其进行处理，这将导致需要删除它并经常重新创建它.

I have a ~20gb database that I will mangle during the testing of a php script that will result in the need to drop it and recreate it quite often.

运行转储的SQL来恢复它需要很多时间，而且我的期限很紧，所以我想知道是否有一种方法可以加快该过程.我认为以下方法可能有效:

Running dumped SQL to restore it takes quite a lot of time, and I'm on a tight deadline, so I wondered if there was a method whereby I could speed up the process. I thought the following may work:

最初创建并填充数据库
将其数据文件复制到辅助位置
用我的测试破坏数据库.
删除数据文件，然后将副本复制回还原到原始状态.

Create and populate the database initially
Copy its data files to a secondary location
mangle the database with my testing.
delete the data files and the copy the copies back restoring the original state.

但是我不知道从哪里开始，或者是否有内部发生的事情会阻止此工作.

But I don't know where to start or if there's some internal stuff happening that would prevent this from working.

是否有可能实现上述目标?

Is the above possible, if so how is it achieved?

这不是一个封闭的问题，如果有比我要求的更快的替代方法，请赐教.我愿意接受建议.

This isn't a closed question, if there are faster alternatives to what I'm asking for, please enlighten me. I'm open to suggestions.

谢谢.

推荐答案

您最快的解决方案可能就是通过文件系统来做到这一点.

Your fastest solution is probably to just do this via the file system.

停止服务器，并在整个数据库群集中进行本地复制，即$ PGDATA(含)以下的所有内容.启动服务器并进行操作.当需要刷新数据库时，请停止服务器并从备份位置将文件复制回.请注意，这会影响整个群集，因此，如果同一群集中的其他数据库正在生产中使用，则无法执行此操作:所有内容均以首次备份时的状态冻结.

Stop the server and make a local copy of your entire database cluster, i.e. everything under $PGDATA, inclusive. Start the server and do your mangling. When you need to refresh your database, stop the server and copy the files back in from your backup location. Note that this affects the entire cluster, so you cannot do this if other databases in the same cluster are in production use: everything is frozen in the state it was in when you first made the backup.

另一种方法是在二进制模式下使用pg_dump，但可能比手动方法慢很多.这是唯一需要保留集群中其他数据库的解决方案.

The alternative is to use pg_dump in binary mode, but probably quite a bit slower than the manual method. It is the only solution if other databases in the cluster need to be preserved.

这篇关于是否可以通过简单地交换一些文件来恢复Postgres数据库的速度?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！