restores to earlier versions not working.
(c) superuser can only run pg_dump.
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.
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 ...
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With