postgresql 如何将 postgres 数据库恢复为另一个数据库名称
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19223522/
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
How to restore postgres database into another database name
提问by newBike
I use the postgres today and got a problem I dump the database that way
我今天使用 postgres 遇到了一个问题，我以这种方式转储了数据库
pg_dump zeus_development -U test > zeus_development.dump.out
what if I wnat to restore to another database zeus_production
如果我想恢复到另一个数据库 zeus_production 怎么办
How could I do?
我怎么办？
回答by MatheusOl
Simple, first create your database using template0as your template database:
createdb -U test -T template0 zeus_production
Then, restore your dump on this database:
然后，在此数据库上恢复转储：
psql -U test zeus_production -f /path/to/zeus_development.dump.out
When restoring, always use template0explicit, as it is always an empty and unmodifiable database. If you don't use an explicit template, PostgreSQL will assume template1, and if it has some objects, like a table or function that your dumped database already has, you will get some errors while restoring.
还原时，始终使用template0显式，因为它始终是一个空且不可修改的数据库。如果您不使用显式模板，PostgreSQL 将假定template1，并且如果它有一些对象，例如您转储的数据库已经拥有的表或函数，您将在恢复时遇到一些错误。
Nonetheless, even if you were restoring on a database with the same name (zeus_development) you should create (or recreate) it the same way. Unless you used -Coption while dumping (or -Cof pg_restoreif using a binary dump), which I don't recommend, because will give you less flexibility (like restoring on a different database name).
尽管如此，即使您正在恢复具有相同名称 ( zeus_development)的数据库，您也应该以相同的方式创建（或重新创建）它。除非你使用的-C选项，同时倾销（或-C中pg_restore如果使用的是二进制转储），我不建议，因为会给你少的灵活性（如恢复在不同的数据库名）。
回答by pedz
The PostgresSQL documentation has influenced me to use the custom format. I've been using it for years and it seems to have various advantages but your mileage may vary. That said, here is what worked for me:
PostgresSQL 文档影响了我使用自定义格式。我已经使用它多年了，它似乎有各种优点，但您的里程可能会有所不同。也就是说，这对我有用：
pg_restore --no-owner --dbname postgres --create ~/Desktop/pg_dump
psql --dbname postgres -c 'ALTER DATABASE foodog_production RENAME TO foodog_development'
There was no foodog_developmentnor foodog_productiondatabases existing before the sequence.
在序列之前没有foodog_development也没有foodog_production数据库存在。
This restores the database from the dump (~/Desktop/pg_dump) which will create it with the name it was dumped as. The rename names the DB to whatever you want.
这将从转储 ( ~/Desktop/pg_dump) 中恢复数据库，这将使用它被转储的名称创建它。重命名将数据库命名为您想要的任何名称。
The --no-ownermay not be needed if your user name is the same on both machines. In my case, the dump was done as user1and the restore done as user2. The new objects need to be owned by user2and --no-ownerachieves this.
的--no-owner，如果你的用户名是在两台机器上同样可能不需要。在我的情况下，转储完成，user1恢复完成user2。新对象需要由其拥有user2并--no-owner实现这一点。
回答by Ricardo Coelho
Isn't it easier to simply do the following?
简单地执行以下操作不是更容易吗？
createdb -U test -T zeus_development zeus_production
回答by mivk
This has an answer on dba.stackexchange, which I reproduce here:
这在 dba.stackexchange 上有一个答案，我在这里复制：
Let's define a few variables to make the rest easier to copy/paste
让我们定义一些变量，使其余的更容易复制/粘贴
old_db=my_old_database
new_db=new_database_name
db_dump_file=backups/my_old_database.dump
user=postgres
The following assumes that your backup was created with the "custom" format like this:
以下假设您的备份是使用“自定义”格式创建的，如下所示：
pg_dump -U $user -F custom $old_db > "$db_dump_file"
To restore $db_dump_fileto a new database name $new_db:
要恢复$db_dump_file到新的数据库名称$new_db：
dropdb -U $user --if-exists $new_db
createdb -U $user -T template0 $new_db
pg_restore -U $user -d $new_db "$db_dump_file"
回答by jethro
Here's a hackyway of doing it, that onlyworks if you can afford the space and time to use regular .sqlformat, and if you can safely sedout your database name and user.
这里有一个hacky 的方法，只有当你有足够的空间和时间来使用常规.sql格式，并且你可以安全地sed列出你的数据库名称和用户时，它才有效。
$ pg_dump -U my_production_user -h localhost my_production > my_prod_dump.sql
$ sed -i 's/my_production_user/my_staging_user/g' my_prod_dump.sql
$ sed -i 's/my_production/my_staging/g' my_prod_dump.sql
$ mv my_prod_dump.sql my_staging_dump.sql
$ sudo su postgres -c psql
psql> drop database my_staging;
psql> create database my_staging owner my_staging_user;
psql> \c my_staging;
psql> \i my_staging_dump.sql
