**The Quick Solution**

The problem is that it&#39;s trying to perform local [`peer`][1] authentication based on your current username. If you would like to use a password you must specify the hostname with `-h`.

    pg_dump dbname -U username -h localhost -F c

**Explanation**

This is due to the following in your [`pg_hba.conf`][2]

    local   all             all                                     peer
    host    all             all             127.0.0.1/32            md5

This tells Postgres to use `peer` authentication for local users which requires the postgres username to match your current system username. The second line refers to connections using a hostname and will allow you to authenticate with a password via the [`md5`][3] method.

**My Preferred Development Config**

**NOTE**: This should only be used on single-user workstations. This could lead to a major security vulnerability on a production or multi-user machine.

When developing against a local postgres instance I like to change my local authentication method to [`trust`][4]. This will allow connecting to postgres via a local unix socket as any user with no password. It can be done by simply changing `peer` above to `trust` and reloading postgres.

&lt;!-- language: lang-none --&gt;

    # Don&#39;t require a password for local connections
    local   all             all                                     trust


  [1]: http://www.postgresql.org/docs/9.1/static/auth-methods.html#AUTH-PEER
  [2]: http://www.postgresql.org/docs/9.1/static/auth-pg-hba-conf.html
  [3]: http://www.postgresql.org/docs/9.1/static/auth-methods.html#AUTH-PASSWORD
  [4]: http://www.postgresql.org/docs/9.1/static/auth-methods.html#AUTH-TRUST

Sometimes you can use

    sudo -u postgres pg_dump ...

I want to mock a static method m1 from a class which contains 2 static methods, m1 and m2. And I want the method m1 to return an object.

I tried the following

1)  

    PowerMockito.mockStatic(Static.class, new Answer&lt;Long&gt;() {
             @Override
             public Long answer(InvocationOnMock invocation) throws Throwable {
                return 1000l;
             }
          });

This is calling both m1 and m2, which has a different return type, so it gives a return type mismatch error.

2) `PowerMockito.when(Static.m1(param1, param2)).thenReturn(1000l);`
 But this is not called when m1 is executed.

3) `PowerMockito.mockPartial(Static.class, &quot;m1&quot;);` 
Gives compiler error that mockPartial not available, which I got from http://code.google.com/p/powermock/wiki/MockitoUsage.

PowerMockito mock single static method and return object

How to add more than one parameter in Twig path?&lt;br&gt;
Say you have this route :

    article_show:
        pattern:  /article/{slug}
        defaults: { _controller: AcmeArticleBundle:Article:show }

You can do this in your twig template :

    {{ path(&#39;article_show&#39;, { &#39;slug&#39;: article.slug }) }}

but what if you have this in your routing file:

    _files_manage:
        pattern: /files/management/project={idproject}&amp;user={iduser}
        defaults: { _controller: AcmeTestBundle:File:manage }

It looks like they didn&#39;t cover this in their documentation.




Add more than one parameter in Twig path

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 have tried using host variable <code>PGPASSWORD</code> and <code>.pgpass</code> and neither of these two will allow me to authenticate to the database. I have <code>chmod</code>'d <code>.pgpass</code> to appropriate permissions and also tried:

<pre><code class="hljs language-ini">export PGPASSWORD=mypass and PGPASSWORD=mypass
</code></pre>
The password DOES contain a <code>\</code> however I was encasing it in single quotes <code>PGPASS='mypass\'</code> and it still will not authenticate.
I'm running:
<pre><code class="hljs">pg_dump dbname -U username -Fc
</code></pre>
and I still receive
<pre><code class="hljs language-vbnet">pg_dump: [archiver (db)] connection to database "dbname" failed: FATAL: Peer authentication failed for user "username"
</code></pre>

I am importing data from a table which has raw feeds in Varchar, I need to import a column in varchar into a string column. I tried using the `&lt;column_name&gt;::integer` as well as `to_number(&lt;column_name&gt;,&#39;9999999&#39;)` but I am getting errors, as there are a few empty fields, I need to retrieve them as empty or null into the new table.

Typecast string to integer

I am looking for some docs and/or examples for the new JSON functions in PostgreSQL 9.2. 

Specifically, given a series of JSON records:

    [
      {name: &quot;Toby&quot;, occupation: &quot;Software Engineer&quot;},
      {name: &quot;Zaphod&quot;, occupation: &quot;Galactic President&quot;}
    ]

How would I write the SQL to find a record by name?

In vanilla SQL:

    SELECT * from json_data WHERE &quot;name&quot; = &quot;Toby&quot;

The official dev manual is quite sparse:

 - http://www.postgresql.org/docs/devel/static/datatype-json.html
 - http://www.postgresql.org/docs/devel/static/functions-json.html

###Update I

I&#39;ve put together a [gist detailing what is currently possible with PostgreSQL 9.2][1].
Using some custom functions, it is possible to do things like:

    SELECT id, json_string(data,&#39;name&#39;) FROM things
    WHERE json_string(data,&#39;name&#39;) LIKE &#39;G%&#39;;

###Update II

I&#39;ve now moved my JSON functions into their own project:

[**PostSQL**][2] - a set of functions for transforming PostgreSQL and PL/v8 into a totally awesome JSON document store


  [1]: https://gist.github.com/2715918
  [2]: https://github.com/tobyhede/postsql

How do I query using fields inside the new PostgreSQL JSON datatype?

i have gwt application connect to postgres DB at the backend, and a java class &#39;Judgement&#39; mapping the table &#39;judgements&#39; in DB, when i tried to persistent a judgement into db, it threw the following errors:

    Caused by: org.hibernate.exception.SQLGrammarException: could not get next sequence value
    ...
    Caused by: org.postgresql.util.PSQLException: ERROR: relation &quot;hibernate_sequence&quot; does not exist

my Judgement class looks like this

    @Entity
    @Table(name = &quot;JUDGEMENTS&quot;)
    public class Judgement implements Serializable, Cloneable {
    
    	private static final long serialVersionUID = -7049957706738879274L;
    	
    	@Id
    	@GeneratedValue(strategy = GenerationType.AUTO)
    	@Column(name = &quot;JUD_ID&quot;)
    	private Long _judId;
    ...


and my table judgements is: 

       Column    |            Type             |                        Modifiers                        
    -------------+-----------------------------+---------------------------------------------------------
     jud_id      | bigint                      | not null default nextval(&#39;judgements_id_seq&#39;::regclass)
     rating      | character varying(255)      | 
     last_update | timestamp without time zone | 
     user_id     | character varying(255)      | 
     id          | integer                     | 
    Indexes:
        &quot;judgements_pkey&quot; PRIMARY KEY, btree (jud_id)
    Foreign-key constraints:
        &quot;judgements_id_fkey&quot; FOREIGN KEY (id) REFERENCES recommendations(id)
        &quot;judgements_user_id_fkey&quot; FOREIGN KEY (user_id) REFERENCES users(user_id)

and i have a SEQUENCE name &#39;judgements_id_seq&#39; in DB

can anyone tell me what&#39;s wrong??? thanks.

hibernate could not get next sequence value

    &lt;?php

    try {
       $dbh = new PDO(&#39;pgsql:host=localhost;port=5432;dbname=###;user=###;password=##&#39;);
       echo &quot;PDO connection object created&quot;;
    }
    catch(PDOException $e)
    {
          echo $e-&gt;getMessage();
    }

    ?&gt;

I get the error message &quot;Could Not Load Driver&quot;

How do I enable php to work with postgresql?

On Heroku, I am trying to copy the production database into my staging app using the pgbackups addon. I followed the instructions on the addon page: https://devcenter.heroku.com/articles/pgbackups

First I captured the DB:

    heroku pgbackups:capture --app production-app 

That worked:

    HEROKU_POSTGRESQL_PURPLE (DATABASE_URL)  ----backup---&gt;  b238

    Capturing... done
    Storing... done

However when I try to restore it on the staging app:

    heroku pgbackups:restore DATABASE `heroku pgbackups:url --app production-app` --remote staging

I get the following error message:

    DATABASE_URL does not match any of your databases
     !    Could not resolve database DATABASE
     !    
     !    Available databases: 


I have also tried typing in the full URL:

     heroku pgbackups:url b238 --app production-app
     heroku pgbackups:restore DATABASE &quot;https://s3.amazonaws.com/...&quot; --remote staging

and also tried naming the app (instead of --remote staging):

    heroku pgbackups:restore DATABASE `heroku pgbackups:url --app production-app` --app staging-app

None of these worked. It&#39;s interesting to note that the error message says there are no &quot;Available databases&quot;. I&#39;m assuming it is referring to the staging app which is indeed empty. If I type:

    heroku pgbackups
I get:

     !    No backups. Capture one with `heroku pgbackups:capture`.

To find the available backups (production), I need to type:

    heroku pgbackups --app production-app
and I get the list of current backups. I don&#39;t know if this is normal or even if it is related to the problem, but I thought I should mention it. 

I have read and tried every answer here on SO but nothing worked. Any ideas?


How do I transfer production database to staging on Heroku using pgbackups? Getting error

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 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	Kosmonaut	View Question on Stackoverflow
Solution 1 - Postgresql	Jim Mitchener	View Answer on Stackoverflow
Solution 2 - Postgresql	Roland Pihlakas	View Answer on Stackoverflow

How can I get pg_dump to authenticate properly

Postgresql Problem Overview

Postgresql Solutions

Solution 1 - Postgresql

Solution 2 - Postgresql

Add more than one parameter in Twig path

PowerMockito mock single static method and return object

Attributions