You have to add the name of a valid database to initially connect to or it will just dump the contents to STDOUT:

    pg_restore --create --exit-on-error --verbose --dbname=postgres &lt;backup_file&gt;



This is still confusing, I attempted to execute this thing that the --dbname should be the db I want to create. 

    pg_restore --create --exit-on-error --verbose --dbname=jiradb jiradb.tar

WRONG!!

It should literally be --dbname=postgres, the --create then will create the real db from the name in the file. In my case, I restored from a tar backup with 

    pg_restore --create --exit-on-error --verbose --dbname=postgres jiradb.tar

Is there any way to configure sqlite3 so that the headers will display by default?

I know I can use `.headers on` to turn on headers, but I have to keep typing it every time I launch the client because the setting doesn&#39;t stick between sessions.  I want the headers to be on permanently.

How to configure sqlite to display headers by default

&gt; **Possible Duplicate:**  
&gt; [Word-wrap in a html table](https://stackoverflow.com/questions/1258416/word-wrap-in-a-html-table)  

&lt;!-- End of automatically inserted text --&gt;

This text behaves exactly the way I want on Google Chrome (and other modern browsers):

    &lt;div style=&quot;border: 1px solid black; width:100%; word-wrap: break-word;&quot;&gt;
      &lt;p&gt;
        aaaaaaaaaaaaaaaa
        bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
      &lt;/p&gt;
    &lt;/div&gt;

 1. When the browser is wide enough, a+ and b+ are on the same line. 

        aaaaaaaaaaaaaaaa bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
 
 2. As you narrow the browser, a+ and b+ are put on separate lines.

        aaaaaaaaaaaaaaaa
        bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb

 3. When b+ can no longer fit, it is broken and put on two lines (for a total of three lines).

        aaaaaaaaaaaaaaaa
        bbbbbbbbbbbbbbbbbbbbbbbb
        bbbbbbbb

That&#39;s all great.

In my situation, however, this is not a `div` but a `table`, like so:

    &lt;table style=&quot;border:1px solid black; width:100%; word-wrap:break-word;&quot;&gt;
      &lt;tr&gt;
        &lt;td&gt;
          &lt;p&gt;
            aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
            bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
          &lt;/p&gt;
        &lt;/td&gt;
      &lt;/tr&gt;
    &lt;/table&gt;

In this case, #1 and #2 happen, but not #3. That is, the table stops narrowing after step 2 and step 3 doesn&#39;t ever happen. The break-word doesn&#39;t seem to be filtering down.

Does anyone know how make #3 happen?  The solution only need work in Chrome, but it it also worked in other browsers that would be even better.

P.S. &quot;Don&#39;t use tables&quot; is not helpful.

Using &quot;word-wrap: break-word&quot; within a table

I have a Postgres 8.4 database on a linux server that I have dumped using the following command:

    pg_dump --format=c --exclude-table=log --file=/path/to/output my_db

I then ftp the created file to my local Windows 7 machine and attempt to restore the file to my local Postgres 8.4 instance using the following command:

    pg_restore --create --exit-on-error --verbose c:\path\to\file

The restore command generates plenty of output claiming it created my database, connected to it, and then created all the other tables as expected.  However, when I view the databases on my local machine through pgAdmin the restored database doesn&#39;t exist at all.

In an attempt to troubleshoot I tried the following command:

    pg_restore --create --exit-on-error --verbose --host=blahblah --username=no_one c:\path\to\file

When I run this command even though the host and username given are complete nonsense I still get the exact same output from the command without any errors.

Has anyone run into this before or know what could by causing this?

Why is pg_restore returning successfully but not actually restoring my database?

I have a Postgres 8.4 database on a linux server that I have dumped using the following command:
<pre><code class="hljs language-css">pg_dump --format=c --exclude-table=log --file=/path/to/output my_db
</code></pre>
I then ftp the created file to my local Windows 7 machine and attempt to restore the file to my local Postgres 8.4 instance using the following command:
<pre><code class="hljs language-vbnet">pg_restore --create --exit-on-error --verbose c:\path\to\file
</code></pre>
The restore command generates plenty of output claiming it created my database, connected to it, and then created all the other tables as expected. However, when I view the databases on my local machine through pgAdmin the restored database doesn't exist at all.
In an attempt to troubleshoot I tried the following command:
<pre><code class="hljs language-css">pg_restore --create --exit-on-error --verbose --host=blahblah --username=no_one c:\path\to\file
</code></pre>
When I run this command even though the host and username given are complete nonsense I still get the exact same output from the command without any errors.
Has anyone run into this before or know what could by causing this?

I have changed database from [tag:PostgreSQL] to [tag:MySQL], but I don&#39;t know how to transfer data from one to other.

Does anyone know any command by which I can copy/migrate my database from [tag:PostgreSQL] to [tag:MySQL]?

Migrate database from Postgres to MySQL

I need to insert rows into PG one of the fields is date and time with time stamp, this is the time of incident, so I can not use --&gt; current_timestamp function of Postgres at the time of insertion, so how can I then insert the time and date which I collected before into pg row in the same format as it would have been created by current_timestamp at that point in time.


How to insert current_timestamp into Postgres via python

Say I&#39;ve created a database in pgAdmin, but I want to export a CREATE sql file.

How would I go about generating the dump?

Export a CREATE script for a database

I am using the Ruby on Rails 3.1 pre version. I like to use PostgreSQL, but the problem is installing the `pg` gem. It gives me the following error:

    $ gem install pg
    Building native extensions.  This could take a while...
    ERROR:  Error installing pg:
        ERROR: Failed to build gem native extension.

            /home/u/.rvm/rubies/ruby-1.9.2-p0/bin/ruby extconf.rb
    checking for pg_config... no
    No pg_config... trying anyway. If building fails, please try again with
     --with-pg-config=/path/to/pg_config
    checking for libpq-fe.h... no
    Can&#39;t find the &#39;libpq-fe.h header
    *** extconf.rb failed ***
    Could not create Makefile due to some reason, probably lack of
    necessary libraries and/or headers.  Check the mkmf.log file for more
    details.  You may need configuration options.

    Provided configuration options:
        --with-opt-dir
        --without-opt-dir
        --with-opt-include
        --without-opt-include=${opt-dir}/include
        --with-opt-lib
        --without-opt-lib=${opt-dir}/lib
        --with-make-prog
        --without-make-prog
        --srcdir=.
        --curdir
        --ruby=/home/u/.rvm/rubies/ruby-1.9.2-p0/bin/ruby
        --with-pg
        --without-pg
        --with-pg-dir
        --without-pg-dir
        --with-pg-include
        --without-pg-include=${pg-dir}/include
        --with-pg-lib
        --without-pg-lib=${pg-dir}/lib
        --with-pg-config
        --without-pg-config
        --with-pg_config
        --without-pg_config


    Gem files will remain installed in /home/u/.rvm/gems/ruby-1.9.2-p0/gems/pg-0.11.0 for inspection.
    Results logged to /home/u/.rvm/gems/ruby-1.9.2-p0/gems/pg-0.11.0/ext/gem_make.out

How do I solve this problem?


Can&#39;t find the &#39;libpq-fe.h header when trying to install pg gem

I&#39;m not sure if its standard SQL:  

     INSERT INTO tblA 
     (SELECT id, time 
        FROM tblB 
       WHERE time &gt; 1000)  

What I&#39;m looking for is: **what if tblA and tblB are in different DB Servers**.

Does PostgreSql gives any utility or has any functionality that will help to use `INSERT query with PGresult struct`  

I mean `SELECT id, time FROM tblB ...` will return a `PGresult*` on using `PQexec`. Is it possible to use this struct in another `PQexec` to execute an INSERT command.  

EDIT:  
If not possible then I would go for extracting the values from PQresult* and create a multiple INSERT statement syntax like:

    INSERT INTO films (code, title, did, date_prod, kind) VALUES
        (&#39;B6717&#39;, &#39;Tampopo&#39;, 110, &#39;1985-02-10&#39;, &#39;Comedy&#39;),
        (&#39;HG120&#39;, &#39;The Dinner Game&#39;, 140, DEFAULT, &#39;Comedy&#39;); 

Is it possible to create a prepared statement out of this!! :(

postgresql: INSERT INTO ... (SELECT * ...)

I have tried using host variable `PGPASSWORD` and `.pgpass` and neither of these two will allow me to authenticate to the database. I have `chmod`&#39;d `.pgpass` to appropriate permissions and also tried:

&lt;!-- language-all: lang-none --&gt;

    export PGPASSWORD=mypass and PGPASSWORD=mypass

The password DOES contain a `\` however I was encasing it in single quotes `PGPASS=&#39;mypass\&#39;` and it still will not authenticate.

I&#39;m running:

    pg_dump dbname -U username -Fc

and I still receive 

    pg_dump: [archiver (db)] connection to database &quot;dbname&quot; failed: FATAL:  Peer authentication failed for user &quot;username&quot;


How can I get pg_dump to authenticate properly

I am trying to dump a Postgresql database using the **pg_dump** tool. 

    $ pg_dump books &gt; books.out

How ever i am getting this error. 

    pg_dump: server version: 9.2.1; pg_dump version: 9.1.6
    pg_dump: aborting because of server version mismatch

The `--ignore-version` option is now deprecated and really would not be a a solution to my issue even if it had worked.

How can I upgrade **pg_dump** to resolve this issue?


Postgresql 9.2 pg_dump version mismatch

I have a ton of postgresql dump files I need to peruse through for data. Do I have to install Postgresql and &quot;recover&quot; each one of them into new databases one by one? Or I&#39;m hoping there&#39;s a postgresql client that can simply open them up and I can peek at the data, maybe even run a simple SQL query?

The dump files are all from a Postgresql v9.1.9 server.

Or maybe there&#39;s a tool that can easily make a database &quot;connection&quot; to the dump files?

***UPDATE***: These are **not text files**. They are **binary**. They come from Heroku&#39;s backup mechanism, this is [what Heroku says about how they create their backups][1]:

&gt; PG Backups uses the native pg_dump PostgreSQL tool to create its
&gt; backup files, making it trivial to export to other PostgreSQL
&gt; installations.


  [1]: https://devcenter.heroku.com/articles/heroku-postgres-import-export

Easy way to view postgresql dump files?

I do want to search for some data inside a database dump but these dumps are using the binary-compressed format (`PGDMP` header). 

How can I convert these to SQL without restoring them?

How do I convert a binary pgdump (compressed) to a plain SQL file?

I&#39;m trying to pg_dump a SQL database on a remote server in our DMZ. There are 2 problems. 

1) there is not a lot of space left on the remote server so the normal command run to locally backup the database 
`pg_dump -C database &gt; sqldatabase.sql.bak`  won&#39;t work due to space issues. 


2) I also can&#39;t run the other version of pg_dump command to dump database from remote server to local server using: 

    pg_dump -C -h remotehost -U remoteuser db_name | psql localhost -U localuser db_name

as the server is in our DMZ and port 5432 is blocked. What I&#39;m looking to see is if it is possible to pg_dump the database and immediatly save it (ssh or some other form) as a file to a remote server. 
What I was trying was:   `pg_dump -C testdb | ssh admin@ourserver.com | &gt; /home/admin/testdb.sql.bak` 

Does anyone know if what i am trying to achieve is possible? 

pg_dump postgres database from remote server when port 5432 is blocked

I take backup using 

    pg_dump db_production &gt; postgres_db.dump

and then I copy it to localhost using scp.

Now when I import on my local db it gives an error 

    pg_restore: [archiver] input file appears to be a text format dump. Please use psql.

by using commad line

    pg_restore -d db_development postgres_db.dump


input file appears to be a text format dump. Please use psql

I just upgraded to postgres 10.2 on mac os which matches 10.2 on heroku.  I&#39;m trying to download a copy of the database and restore it locally.  Before the upgrade the restore would work fine.

I run

    pg_restore --verbose --clean --no-acl --no-owner -h localhost -d database_name backup.dump

but I am getting this error:

&gt;pg_restore: [archiver] unsupported version (1.13) in file header

The database appears to be working OK.  It&#39;s a rails app and I upgraded the pg gems.  I can run `rake db:create` and `db:migrate` just fine.  


Getting &quot;[archiver] unsupported version (1.13) in file header&quot; when running pg_restore

I have a live server and development box, call them live and dev respectively both running postgresql. I can see both and manage both with pgadmin4 without trouble, and both are fully functional the one behing a live website and the other when I run by website in debug mode on the my dev box. Pretty ordinary setup.

For years I have been running the same bash script I wrote that dumps the live database then restores it on the dev box so I have the latest live snapshot to work with.

Today this fails me with the titled message:
```
pg_restore: [archiver] unsupported version (1.14) in file header
```

I have tried to diagnose this, and searched extensively on-line but am bedeviled and have failed so here I am cap in hand for expertise.

To help I will share the following:

```
$ pg_dump --version
pg_dump (PostgreSQL) 10.11 (Ubuntu 10.11-1.pgdg18.04+1)
$ pg_restore --version
pg_restore (PostgreSQL) 10.11 (Ubuntu 10.11-1.pgdg18.04+1)
$ pg_dump --host=live.lan --port=5432 --dbname=mydb --username=myuser --format=custom &gt; test.backup
$ ls -l test.backup 
-rw-r--r-- 1 bernd bernd 2398358 Dec 23 23:40 test.backup
$ file test.backup 
test.backup: PostgreSQL custom database dump - v1.14-0
$ pg_restore --dbname=mydb test.backup 
pg_restore: [archiver] unsupported version (1.14) in file header
```

Given pg_dump and pg_restore are identical versions and:

```
$ which pg_dump
/usr/bin/pg_dump
$ which pg_restore
/usr/bin/pg_restore
$ ls -l /usr/bin/pg_dump /usr/bin/pg_restore
lrwxrwxrwx 1 root root 37 Nov 14 23:23 /usr/bin/pg_dump -&gt; ../share/postgresql-common/pg_wrapper
lrwxrwxrwx 1 root root 37 Nov 14 23:23 /usr/bin/pg_restore -&gt; ../share/postgresql-common/pg_wrapper
```

I can see they are not just identical versions but are run by the same wrapper script (which happens to be a perl script - now that&#39;s a language you don&#39;t see much anymore and I used to code in extensively)

So I&#39;m left totally perplexed. Thinking there may be a version issue with the live machine:

```
$ ssh live.lan
Welcome to Ubuntu 18.04.3 LTS (GNU/Linux 4.15.0-72-generic x86_64)
$ which pg_dump
/usr/bin/pg_dump
$ which pg_restore
/usr/bin/pg_restore
$ pg_dump --version
pg_dump (PostgreSQL) 10.10 (Ubuntu 10.10-0ubuntu0.18.04.1)
$ pg_restore --version
pg_restore (PostgreSQL) 10.10 (Ubuntu 10.10-0ubuntu0.18.04.1)
```

I can see the live box does have slightly older version of pg_dump (which would only matter if pg_dump on my dev box somehow used a RPC to the live box to run its pg_dump).

Now there is maybe a small clue in the fact that my dev box has seen a few postgresql upgrades come through and so for example:

```
$ pg_lsclusters
Ver Cluster Port Status Owner    Data directory              Log file
10  main    5432 online postgres /var/lib/postgresql/10/main /var/log/postgresql/postgresql-10-main.log
11  main    5433 online postgres /var/lib/postgresql/11/main /var/log/postgresql/postgresql-11-main.log
12  main    5434 online postgres /var/lib/postgresql/12/main /var/log/postgresql/postgresql-12-main.log
```

The 11 and 12 clusters remain unused as evidenced by empty log files. I&#39;m using 10. But I do notice that:

```
$ psql --version
psql (PostgreSQL) 12.1 (Ubuntu 12.1-1.pgdg18.04+1)
$ ssh live.lan
Welcome to Ubuntu 18.04.3 LTS (GNU/Linux 4.15.0-72-generic x86_64)
$ psql --version
psql (PostgreSQL) 10.10 (Ubuntu 10.10-0ubuntu0.18.04.1)
```

which is mildly fishy but again not obviously a cause or related:

 1. I am using pg_dump not psql
 2. I am using only the dev boxes pg tools not the live boxes (they should be irrelevant, the whole data transfer theoretically over port 5432 on the live box which delivers a databse dump to pg_dump on my dev box.

Here are the clusters on the love box and it&#39;s over port 5432 that on live.lan I&#39;m running pg_dump!

```
$ pg_lsclusters 
Ver Cluster Port Status Owner    Data directory           Log file
10  main    5432 online postgres /data/postgresql/10/main /var/log/postgresql/postgresql-10-main.log
```

I am deeply perplexed and hamstrung at present by this. Would deeply appreciate forward moving clues. If I am compelled to fish around in the dark I will probbaly uninstall postgres 11 and 12 again and see if that helps, else I will end up having to trace `/usr/share/postgresql-common/pg_wrapper` to see how and where the two paths of pg_dump and pg_restore diverge down incompatible version paths. 

**Update:**

A further clue I have uncovered, that permits me a workaround but simply deepens the mystery is as follows:

```
$ sudo -u postgres pg_dump --host=live.lan --port=5432 --dbname=mydb --username=myuser --format=custom &gt; test.backup
$ sudo -u postgres /usr/lib/postgresql/10/bin/pg_dump --host=live.lan --port=5432 --dbname=mydb --username=myuser --format=custom &gt; test2.backup
$ sudo -u postgres pg_restore -l test.backup
pg_restore: [archiver] unsupported version (1.14) in file header
$ sudo -u postgres pg_restore -l test2.backup
... produces listing of contents ...
$ sudo -u postgres pg_dump --version
pg_dump (PostgreSQL) 10.11 (Ubuntu 10.11-1.pgdg18.04+1)
$ sudo -u postgres /usr/lib/postgresql/10/bin/pg_dump --version
pg_dump (PostgreSQL) 10.11 (Ubuntu 10.11-1.pgdg18.04+1)
```

That is perplexing beyond belief. The only possible explanations:

 1. in spite of reporting identical version numbers the two pg_dumps are different. I would rule this out as beyond belief.
 2. pg_dump runs pg_wrapper which runs /usr/lib/postgresql/10/bin/pg_dump with some mystery argument(s) that break it! 

The second is plausible, and will require me to instrument pg_wrapper to diagnose. 

**Update 2:**

And one instrumentation of pg_wrapper later. It eventuates that pg_dump runs pg_wrapper which runs `/usr/lib/postgresql/12/bin/pg_dump` yet it runs `/usr/lib/postgresql/10/bin/pg_restore` ... Go figure! Starting to think this a postgresql version interoperability bug!

**Update 3:**

Looking deeper into `pg_wrapper`, nailed the cause, and yes I&#39;d argue it&#39;s a pg_wrapper bug of a kind though it may be debatable, hardly though IMHO. Here&#39;s what it does:

If `--host` is provided then it uses the newest version of postgresql installed (12 in my case and this is for pg_dump, so pg_dump 12 creates the dump)

If `--host` is not provided then it consults the user config (10 in my case, and this is for pg_restore, so pg_restore 10 is run and it can&#39;t read a file created by pg_dump 12).

So why is this a bug? Because I have a use config, and I&#39;d like it respected whether or not I&#39;m talking to a remote host. More to the point if I specify a host I certainly don&#39;t expect to use the latest local version ignoring local config. I&#39;d expect either to respect local config (as is the case when no remote host is specified) or try to match the rmeote hosts version. Arbitrarily leaning on the latest installed version is IMHO deeply questionable.

BUT it turns out there is a workaround that works. Essentially instead of:

```
sudo -u postgres pg_restore -l test.backup
```

this works:

```
sudo -u postgres pg_restore --host=localhost -l test.backup
```

By specifying the host ironically we force it to ignore local configs and use the newest version of pg_restore which seems to work fine restoring to a PG 10 cluster. 

Content Type	Original Author	Original Content on Stackoverflow
Question	Mike Deck	View Question on Stackoverflow
Solution 1 - Postgresql	Matthew Wood	View Answer on Stackoverflow
Solution 2 - Postgresql	tgunr	View Answer on Stackoverflow

Why is pg_restore returning successfully but not actually restoring my database?

Postgresql Problem Overview

Postgresql Solutions

Solution 1 - Postgresql

Solution 2 - Postgresql

Using "word-wrap: break-word" within a table

How to configure sqlite to display headers by default

Attributions