将Heroku上的Postgres和转储单个表转储文件 [英] Postgres on Heroku and dumping single table to dump file

问题描述

我在Heroku上使用Postgres，并且需要从我的生产数据库中转储一张表并将其转储到我的临时数据库中。我安装了heroku工具带，但不知道如何将单个数据库表转储到我的临时数据库中。

I am using Postgres on Heroku and am needing to dump one table from my production DB and dump it into my staging DB. I have the heroku tool belt installed, but am not sure how to dump a single db table to import into my staging db.

推荐答案

您可以转储单个数据表，如下所示：

You can dump a single table of data like so:

$ pg_dump --no-acl --no-owner -h [host ip].compute-1.amazonaws.com -U [user name] -t [table name] --data-only [database name] > table.dump

您可以获得所有需要的值：

You can get all of the values needed with this:

$ heroku pg:credentials:url [DATABASE] -a [app_name]
Connection info string:
   "dbname=[database name] host=[host ip].compute-1.amazonaws.com port=5432 user=[user name] password=[password] sslmode=require"
Connection URL:
    postgres://[username]:[password]@[host ip].compute-1.amazonaws.com:5432/[database name]

这会提示您输入密码。输入它，然后应该继续在本地驱动器上获取文件 table.dump 。

This will prompt you for your password. Enter it, and you should then proceed to get a file table.dump on your local drive.

您可能希望在暂存中截断表：

You probably want to truncate the table on staging:

$ echo "truncate [table];" | heroku pg:psql [DATABASE] -a staging_app

使用该文件，您可以使用 psql 与连接URL： 对 pg：credentials 用于登台应用，然后恢复该表。

With that file, you can use psql with the Connection URL:output of a new call to pg:credentials for the staging app and restore just that table.

$ psql "[pasted postgres:// from pg:credentials:url of staging app]" < table.dump
SET
SET
...
...
...
...
$ 

这篇关于将Heroku上的Postgres和转储单个表转储文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！