snipt

Ctrl+h for KB shortcuts

PostgreSQL console (psql)

pg_dump on a remote server

1
2
3
4
5
6
7
#make a backup from a remote postgresql server
#Instructions from here
#http://www.mkyong.com/database/backup-restore-database-in-postgresql-pg_dumppg_restore/
pg_dump -i -h $REMOTESERVER -p 5432 -U $USERDATABASE -F c -b -v -f "backupdatabase.backup" $DATABASE

#Restore, if you need. You can restore local or remotely
pg_restore -i -h localhost -p 5432 -U $USER -d $DATABASE -v "backupdatabase.backup"
https://snipt.net/embed/0b97c217544a5cf8e64f5d7282b06ed8/
/raw/0b97c217544a5cf8e64f5d7282b06ed8/
0b97c217544a5cf8e64f5d7282b06ed8
psql
PostgreSQL console (psql)
7
2019-05-25T08:07:04
True
False
False
Sep 18, 2012 at 03:43 PM
/api/public/snipt/47336/
pg_dump-on-a-remote-server
<table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><a href="#L-1">1</a> <a href="#L-2">2</a> <a href="#L-3">3</a> <a href="#L-4">4</a> <a href="#L-5">5</a> <a href="#L-6">6</a> <a href="#L-7">7</a></pre></div></td><td class="code"><div class="highlight"><pre><span></span><span id="L-1"><a name="L-1"></a><span class="o">#</span><span class="n">make</span> <span class="n">a</span> <span class="n">backup</span> <span class="k">from</span> <span class="n">a</span> <span class="n">remote</span> <span class="n">postgresql</span> <span class="k">server</span> </span><span id="L-2"><a name="L-2"></a><span class="o">#</span><span class="n">Instructions</span> <span class="k">from</span> <span class="n">here</span> </span><span id="L-3"><a name="L-3"></a><span class="o">#</span><span class="n">http</span><span class="p">:</span><span class="o">//</span><span class="n">www</span><span class="mf">.</span><span class="n">mkyong</span><span class="mf">.</span><span class="n">com</span><span class="o">/</span><span class="k">database</span><span class="o">/</span><span class="n">backup</span><span class="o">-</span><span class="n">restore</span><span class="o">-</span><span class="k">database</span><span class="o">-</span><span class="k">in</span><span class="o">-</span><span class="n">postgresql</span><span class="o">-</span><span class="n">pg_dumppg_restore</span><span class="o">/</span> </span><span id="L-4"><a name="L-4"></a><span class="n">pg_dump</span> <span class="o">-</span><span class="n">i</span> <span class="o">-</span><span class="n">h</span> <span class="err">$</span><span class="n">REMOTESERVER</span> <span class="o">-</span><span class="n">p</span> <span class="mf">5432</span> <span class="o">-</span><span class="n">U</span> <span class="err">$</span><span class="n">USERDATABASE</span> <span class="o">-</span><span class="n">F</span> <span class="n">c</span> <span class="o">-</span><span class="n">b</span> <span class="o">-</span><span class="n">v</span> <span class="o">-</span><span class="n">f</span> <span class="s s-Name">&quot;backupdatabase.backup&quot;</span> <span class="err">$</span><span class="k">DATABASE</span> </span><span id="L-5"><a name="L-5"></a> </span><span id="L-6"><a name="L-6"></a><span class="o">#</span><span class="n">Restore</span><span class="p">,</span> <span class="k">if</span> <span class="n">you</span> <span class="n">need</span><span class="mf">.</span> <span class="n">You</span> <span class="n">can</span> <span class="n">restore</span> <span class="k">local</span> <span class="k">or</span> <span class="n">remotely</span> </span><span id="L-7"><a name="L-7"></a><span class="n">pg_restore</span> <span class="o">-</span><span class="n">i</span> <span class="o">-</span><span class="n">h</span> <span class="n">localhost</span> <span class="o">-</span><span class="n">p</span> <span class="mf">5432</span> <span class="o">-</span><span class="n">U</span> <span class="err">$</span><span class="k">USER</span> <span class="o">-</span><span class="n">d</span> <span class="err">$</span><span class="k">DATABASE</span> <span class="o">-</span><span class="n">v</span> <span class="s s-Name">&quot;backupdatabase.backup&quot;</span> </span></pre></div> </td></tr></table>
pg_dump, pg_restore, postgre, postgreSQL

Text only

passwordless PostgreSQL dumps

1
2
3
4
5
6
# /etc/sudoers
username machine = (postgres) NOPASSWD: /usr/bin/pg_dump
# Make sure no following rules override the above one.
# Now you can:
# [email protected] $ sudo -u postgres pg_dump mydb
# without needing to enter your password.
https://snipt.net/embed/b3eaa5687c0f900d5a349ba3e688ac2e/
/raw/b3eaa5687c0f900d5a349ba3e688ac2e/
b3eaa5687c0f900d5a349ba3e688ac2e
text
Text only
6
2019-06-25T17:44:45
True
False
False
/api/public/snipt/4455/
passwordless-postgresql-dumps
<table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><a href="#L-1">1</a> <a href="#L-2">2</a> <a href="#L-3">3</a> <a href="#L-4">4</a> <a href="#L-5">5</a> <a href="#L-6">6</a></pre></div></td><td class="code"><div class="highlight"><pre><span></span><span id="L-1"><a name="L-1"></a># /etc/sudoers </span><span id="L-2"><a name="L-2"></a>username machine = (postgres) NOPASSWD: /usr/bin/pg_dump </span><span id="L-3"><a name="L-3"></a># Make sure no following rules override the above one. </span><span id="L-4"><a name="L-4"></a># Now you can: </span><span id="L-5"><a name="L-5"></a># [email protected] $ sudo -u postgres pg_dump mydb </span><span id="L-6"><a name="L-6"></a># without needing to enter your password. </span></pre></div> </td></tr></table>
database, pg_dump, postgresql, sudo