Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How can I get pg_dump to authenticate properly

People also ask

Is pg_dump backwards compatible?

restores to earlier versions not working.

Which is appropriate regarding pg_dump?

(c) superuser can only run pg_dump.

Does pg_dump affect performance?

The only impact of pg_dump are the increased I/O load and the long running transaction it creates. The long transaction will keep autovacuum from reclaimimg dead tuples for the duration of the dump. Normally that is no big problem unless you have very high write activity in the database.

Does pg_dump overwrite?

Restoring the data from pg_dump doesn't overwrite the data but it appends the data to the original database. Bookmark this question.


The Quick Solution

The problem is that it's trying to perform local peer 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

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 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. 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.

# Don't require a password for local connections
local   all             all                                     trust

Sometimes you can use

sudo -u postgres pg_dump ...