blocks|key|1264396|text|POSTGRESQL+9.1.12|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1264397|转储：|1264398|pg_dump+-U+user+db_name+>+archive_name.sql|code-block|syntax|javascript|1264399|输入用户密码，然后按enter键。|1264400|恢复：|1264401|psql+-U+user+db_name+<+/directory/archive.sql|1264402|1264403|entityMap^0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|R|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|S|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|T|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|U|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|V|8|@]|9|@]|A|$]]|$1|M|3|N|5|F|7|W|8|@]|9|@]|A|$G|H]]|$1|O|3|J|5|6|7|X|8|@]|9|@]|A|$]]|$1|P|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|Q|$]]

POSTGRESQL 9.1.12

DUMP:

<pre><code>pg_dump -U user db_name &gt; archive_name.sql
</code></pre>

put the user password and press enter.

RESTORE:

<pre><code>psql -U user db_name &lt; /directory/archive.sql
</code></pre>

put the user password and press enter.

blocks|key|1049751|text|下面是我用来恢复数据库的pg_dump版本：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1049752|pg_restore+-h+localhost+-p+5432+-U+postgres+-d+my_new_database+my_old_database.backup|code-block|syntax|javascript|1049753|或者使用psql|1049754|psql+-h+localhost+-U+postgres+-p+5432+my_new_database+<+my_old_database.backup|1049755|其中，-h主机、-p端口、-u登录用户名、数据库的-d名称|1049756|entityMap^0|C|7|0|0|4|4|0|0|3|2|8|2|D|2|P|2|0^^$0|@$1|2|3|4|5|6|7|S|8|@$9|T|A|U|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|V|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|W|8|@$9|X|A|Y|B|C]]|D|@]|E|$]]|$1|M|3|N|5|H|7|Z|8|@]|D|@]|E|$I|J]]|$1|O|3|P|5|6|7|10|8|@$9|11|A|12|B|C]|$9|13|A|14|B|C]|$9|15|A|16|B|C]|$9|17|A|18|B|C]]|D|@]|E|$]]|$1|Q|3|-4|5|6|7|19|8|@]|D|@]|E|$]]]|R|$]]

Below is my version of <code>pg_dump</code> which I use to restore the database:

<pre><code>pg_restore -h localhost -p 5432 -U postgres -d my_new_database my_old_database.backup
</code></pre>

or use <code>psql</code>:

<pre><code>psql -h localhost -U postgres -p 5432 my_new_database &lt; my_old_database.backup
</code></pre>

where <code>-h</code> host, <code>-p</code> port, <code>-u</code> login username, <code>-d</code> name of database

blocks|key|1264800|text|这对我很有效：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1264801|pg_restore+--verbose+--clean+--no-acl+--no-owner+--host=localhost+--dbname=db_name+--username=username+latest.dump|code-block|syntax|javascript|1264802|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

This worked for me:

<pre class="lang-sh prettyprint-override"><code>pg_restore --verbose --clean --no-acl --no-owner --host=localhost --dbname=db_name --username=username latest.dump
</code></pre>

blocks|key|1264365|text|Backup:++$+pg_dump+-U+{user-name}+{source_db}+-f+{dumpfilename.sql}

Restore:+$+psql+-U+{user-name}+-d+{desintation_db}+-f+{dumpfilename.sql}|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1264366|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>Backup: $ pg_dump -U {user-name} {source_db} -f {dumpfilename.sql}

Restore: $ psql -U {user-name} -d {desintation_db} -f {dumpfilename.sql}
</code></pre>

blocks|key|1050007|text|试试这个：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1050008|psql+-U+<username>+-d+<dbname>+-f+<filename>.sql|code-block|syntax|javascript|1050009|从.sql文件恢复DB+psql|1050010|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

try this:

<pre><code>psql -U &lt;username&gt; -d &lt;dbname&gt; -f &lt;filename&gt;.sql
</code></pre>

Restore DB psql from .sql file

blocks|key|1264657|text|1)打开psql终端。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1264658|2)解压缩/解压缩转储文件。|1264659|3)创建空数据库。|1264660|4)使用以下命令恢复.dump文件|1264661|<database_name>-#+\i+<path_to_.dump_file>|code-block|syntax|javascript|1264662|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|Q|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|R|8|@]|9|@]|A|$]]|$1|H|3|I|5|J|7|S|8|@]|9|@]|A|$K|L]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

1) Open psql terminal.

2) Unzip/ untar the dump file.

3) Create an empty database.

4) use the following command to restore the .dump file

<pre><code>&lt;database_name&gt;-# \i &lt;path_to_.dump_file&gt;
</code></pre>

blocks|key|1264864|text|我没有看到这里提到转储文件扩展名(*.dump)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1264865|这个解决方案对我很有效：|1264866|我得到了一个转储文件，需要恢复它。|1264867|首先，我尝试用pg_restore来做这件事，得到了：|offset|length|style|CODE|1264868|pg_restore:+error:+input+file+appears+to+be+a+text+format+dump.+Please+use+psql.|code-block|syntax|javascript|1264869|我用psql做到了这一点，并且工作得很好：|1264870|psql+-U+myUser+-d+myDataBase+<+path_to_the_file/file.dump|1264871|entityMap^0|0|0|0|7|A|0|0|2|4|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|X|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|Y|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|Z|8|@$H|10|I|11|J|K]]|9|@]|A|$]]|$1|L|3|M|5|N|7|12|8|@]|9|@]|A|$O|P]]|$1|Q|3|R|5|6|7|13|8|@$H|14|I|15|J|K]]|9|@]|A|$]]|$1|S|3|T|5|N|7|16|8|@]|9|@]|A|$O|P]]|$1|U|3|-4|5|6|7|17|8|@]|9|@]|A|$]]]|V|$]]

I didnt see here mentions about dump file extension (*.dump).
This solution worked for me:
I got a dump file and needed to recover it.
First I tried to do this with <code>pg_restore</code> and got:
<pre><code>pg_restore: error: input file appears to be a text format dump. Please use psql.
</code></pre>
I did it with <code>psql</code> and worked well:
<pre><code>psql -U myUser -d myDataBase &lt; path_to_the_file/file.dump
</code></pre>

blocks|key|1264730|text|尝试：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1264731|pg_restore+-h+localhost+-p+5432+-U+<username>+-d+<dbname>+-1+<filename>|code-block|syntax|javascript|1264732|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

try:

<pre><code>pg_restore -h localhost -p 5432 -U &lt;username&gt; -d &lt;dbname&gt; -1 &lt;filename&gt;
</code></pre>

blocks|key|1265010|text|恢复转储文件|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1265011|psql+-d+[Dbname]+-U+[UserName]+-p+5432+<+[FileLocation]|code-block|syntax|javascript|1265012|要恢复.SQL文件，请执行以下操作|1265013|pg_restore+-U+[Username]+-d+[Dbname]+-1+[FileLocation]|1265014|如果出现用户身份验证错误，请转到程序文件中PSQL/data文件夹中的pg_hba.conf文件，并将“方法”更改为“信任”。在windows服务中重新启动psql+(Win%2BR+-->+services.msc)。|1265015|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

To restore a dump file
<pre><code>psql -d [Dbname] -U [UserName] -p 5432 &lt; [FileLocation]
</code></pre>
To restore a .SQL file
<pre><code>pg_restore -U [Username] -d [Dbname] -1 [FileLocation]
</code></pre>
If you get user authentication errors, go to the file pg_hba.conf which is in PSQL/data folder in your program files, and change the &quot;METHOD&quot; to &quot;Trust&quot;.
Restart you psql serive in windows services(Win + R --&gt; services.msc).

blocks|key|1264497|text|如下链接所述，您可以使用psql命令恢复转储文件：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1264498|https://www.postgresql.org/docs/8.1/static/backup.html#BACKUP-DUMP-RESTORE|offset|length|1264499|psql+dbname+<+infile|code-block|syntax|javascript|1264500|如果需要设置用户名，只需在命令后添加用户名，如下所示：|1264501|psql+dbname+<+infile+username|1264502|entityMap|0|LINK|mutability|MUTABLE|url^0|0|0|22|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|V|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|W|8|@]|9|@$D|X|E|Y|1|Z]]|A|$]]|$1|F|3|G|5|H|7|10|8|@]|9|@]|A|$I|J]]|$1|K|3|L|5|6|7|11|8|@]|9|@]|A|$]]|$1|M|3|N|5|H|7|12|8|@]|9|@]|A|$I|J]]|$1|O|3|-4|5|6|7|13|8|@]|9|@]|A|$]]]|P|$Q|$5|R|S|T|A|$U|C]]]]

As below link said, you can use psql command for restoring the dump file:

<a href="https://www.postgresql.org/docs/8.1/static/backup.html#BACKUP-DUMP-RESTORE" rel="nofollow noreferrer">https://www.postgresql.org/docs/8.1/static/backup.html#BACKUP-DUMP-RESTORE</a>

<pre><code>psql dbname &lt; infile
</code></pre>

if you need to set username just add the username after the command like:

<pre><code>psql dbname &lt; infile username
</code></pre>

blocks|key|1264615|text|尝试以下命令是否可以帮助您：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1264616|sudo+su+-+yourdbuser
psql
\i+yourbackupfile|code-block|syntax|javascript|1264617|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Try to see if the following commands can help you:

<pre><code>sudo su - yourdbuser
psql
\i yourbackupfile
</code></pre>

blocks|key|1049847|text|我在运行pg_dump时遇到了身份验证问题，所以我移动了转储文件|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1049848|mv+database_dump+/tmp|code-block|syntax|javascript|1049849|添加到temp目录，然后运行|1049850|su+-u+postgres
cd+/tmp
pg_restore+database_dump|1049851|如果您有一个大型数据库转储，您可能只想创建另一个目录，供您的当前用户和postgres用户访问，并将数据库转储文件放入其中。|1049852|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

I was having authentication problems running pg_dump, so I moved my dump file

<pre><code>mv database_dump /tmp
</code></pre>

into the temp directory and then ran

<pre><code>su -u postgres
cd /tmp
pg_restore database_dump
</code></pre>

If you have a large database dump, you may just want to create another directory where your current user and the postgres user can access and putting the database dump file into that.

blocks|key|1264795|text|如果您有一个备份的SQL文件，那么您可以很容易地恢复它。只需遵循下面的说明即可|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1264796|1.+At+first,+create+a+database+using+pgAdmin+or+whatever+you+want+(for+example+my_db+is+our+created+db+name)
2.+Now+Open+command+line+window
3.+Go+to+Postgres+bin+folder.+For+example:++cd+"C:\ProgramFiles\PostgreSQL\pg10\bin"
4.+Enter+the+following+command+to+restore+your+database:+psql.exe+-U+postgres+-d+my_db+-f+D:\Backup\backup_file_name.sql+|code-block|syntax|javascript|1264797|如果需要，输入postgres用户的密码，让Postgres来完成它的工作。然后，您可以检查恢复过程。|1264798|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

If you have a backup SQL file then you can easily Restore it.
Just follow the instructions, given in the below

<pre><code>1. At first, create a database using pgAdmin or whatever you want (for example my_db is our created db name)
2. Now Open command line window
3. Go to Postgres bin folder. For example: cd "C:\ProgramFiles\PostgreSQL\pg10\bin"
4. Enter the following command to restore your database: psql.exe -U postgres -d my_db -f D:\Backup\backup_file_name.sql 
</code></pre>

Type password for your postgres user if needed and let Postgres to do its work. Then you can check the restore process.

blocks|key|1050203|text|Backup==>|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1050204|Option1:在命令中保存备份和密码|1050205|++`1.PGPASSWORD="mypassword"++pg_dump+-U+postgres+-h+localhost+--inserts+mydb>mydb.sql`|code-block|syntax|javascript|1050206|Option2:在命令中执行无密码备份|1050207|++`2.+pg_dump+-U+postgres+-h+localhost+--inserts+mydb>mydb.sql`|1050208|Option3:将备份作为gzip(如果数据库很大)|1050209|+`3.+pg_dump+-U+postgres+-h+localhost+mydb+--inserts+%7C+gzip+>++mydb.gz`|1050210|恢复：|1050211|+`1.+psql+-h+localhost+-d+mydb+-U+postgres+-p+5432+<+mydb.sql`|1050212|entityMap^0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|X|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|Y|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|Z|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|10|8|@]|9|@]|A|$G|H]]|$1|M|3|N|5|6|7|11|8|@]|9|@]|A|$]]|$1|O|3|P|5|F|7|12|8|@]|9|@]|A|$G|H]]|$1|Q|3|R|5|6|7|13|8|@]|9|@]|A|$]]|$1|S|3|T|5|F|7|14|8|@]|9|@]|A|$G|H]]|$1|U|3|-4|5|6|7|15|8|@]|9|@]|A|$]]]|V|$]]

Backup==&gt;
Option1: To take backup along with password in cmd 
<code> 1.PGPASSWORD=&quot;mypassword&quot; pg_dump -U postgres -h localhost --inserts mydb&gt;mydb.sql</code> 
Option2: To take backup without password in cmd 
<code> 2. pg_dump -U postgres -h localhost --inserts mydb&gt;mydb.sql</code> 
Option3: To take backup as gzip(if database is huge) 
<code> 3. pg_dump -U postgres -h localhost mydb --inserts | gzip &gt; mydb.gz</code>
Restore: 
<code> 1. psql -h localhost -d mydb -U postgres -p 5432 &lt; mydb.sql</code>

blocks|key|1050245|text|如果您已经创建了一个名为mydb的新数据库，要使用psql将.sql转储恢复到该数据库，|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1050246|psql+--file=dump.sql+--username=postgres+--host=localhost+--port=5432+mydb|code-block|syntax|javascript|1050247|密码将由psql提示|1050248|连接选项包括|1050249|++-h,+--host=HOSTNAME++++++database+server+host+or+socket+directory+(default:+"/var/run/postgresql")
++-p,+--port=PORT++++++++++database+server+port+(default:+"5432")
++-U,+--username=USERNAME++database+user+name+(default:+"xyz")
++-w,+--no-password++++++++never+prompt+for+password
++-W,+--password+++++++++++force+password+prompt+(should+happen+automatically)|1050250|entityMap^0|C|4|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@$9|T|A|U|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|V|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|W|8|@]|D|@]|E|$]]|$1|M|3|N|5|6|7|X|8|@]|D|@]|E|$]]|$1|O|3|P|5|H|7|Y|8|@]|D|@]|E|$I|J]]|$1|Q|3|-4|5|6|7|Z|8|@]|D|@]|E|$]]]|R|$]]

If you have created a new database named <code>mydb</code>, To restore a .sql dump to that database with psql,
<pre><code>psql --file=dump.sql --username=postgres --host=localhost --port=5432 mydb
</code></pre>
the password will be prompted by psql
The connection options are
<pre><code> -h, --host=HOSTNAME database server host or socket directory (default: &quot;/var/run/postgresql&quot;)
 -p, --port=PORT database server port (default: &quot;5432&quot;)
 -U, --username=USERNAME database user name (default: &quot;xyz&quot;)
 -w, --no-password never prompt for password
 -W, --password force password prompt (should happen automatically)
</code></pre>

blocks|key|1050345|text|无密码提示的最短路径|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1050346|psql+"postgresql://<db_user>:<db_pass>@<ip>:<port>/<db_name>"+<+"backup.sql"|code-block|syntax|javascript|1050347|如果您使用的是Windows操作系统|1050348|psql.exe+"postgresql://<db_user>:<db_pass>@<ip>:<port>/<db_name>"+<+"backup.sql"|1050349|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

<h1>The shortest way with no password prompt</h1>
<pre><code>psql &quot;postgresql://&lt;db_user&gt;:&lt;db_pass&gt;@&lt;ip&gt;:&lt;port&gt;/&lt;db_name&gt;&quot; &lt; &quot;backup.sql&quot;
</code></pre>
<h1>If you are using Windows OS</h1>
<pre><code>psql.exe &quot;postgresql://&lt;db_user&gt;:&lt;db_pass&gt;@&lt;ip&gt;:&lt;port&gt;/&lt;db_name&gt;&quot; &lt; &quot;backup.sql&quot;
</code></pre>

I'm new to postgresql, and locally, I use pgadmin3. On the remote server, however, I have no such luxury.

I've already created the backup of the database and copied it over, but, is there a way to restore a backup from the command line? I only see things related to GUI or to pg_dumps, so, if someone can tell me how to go about this, that'd be terrific!

Restore a postgres backup file using the command line?

我是postgresql的新手，在本地我使用pgadmin3。然而，在远程服务器上，我没有这样的奢侈。我已经创建了数据库的备份并复制了它，但是，有没有一种方法可以从命令行恢复备份？我只看到与图形用户界面或pg_dumps相关的东西，所以，如果有人能告诉我怎么做，那就太好了！

问使用命令行恢复postgres备份文件？
EN

回答 16

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问使用命令行恢复postgres备份文件？EN

回答 16

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问使用命令行恢复postgres备份文件？
EN