PostgreSQL pg_dump not getting past 2k

Databases
1

Hello all,

I have been trying to piece together a command line that will create a “custom” formatted backup.

This is the code used:

pg_dump -F c --dbname=postgresql://postgres:26455462@127.0.0.1:5432/ > c:\Temp\TimAxcys122223.backup

It works without asking for a password (which is what I want) but the file size is only 2k and is basically just the header information.

Can anyone give me a clue as to what is wrong with this code?

Thanks
Tim

2

A shot in the dark, is it possible that the a dump be limited is size to a maximum set in the configuration ? If 2k is the default, the it’s quite small :thinking:

3

Hi Gilles

No, thats not the problem.

This code, which does ask for a password, generates a file that is 4.92Mb

pg_dump -U postgres -W -F c axcys > c:\Temp\TimAxcys122223.backup

I changed from the last code above to the first code so I did not need to input separately the password.

Tim

4

Without a username I doubt that Postgres can figure out whether you have permission to dump the data.

5

Hello Tim,
if you omit the -u postgres (or another user within postgres with the needed rights) postgre uses the logged in (windows/linux/mac) user.

The password is not needed if you set the method in pb_hba.conf to ‘trust’.
This is not really recommended. If you want to set it I would only allow ‘localhost’ or better ‘127.0.0.1/32’ like:

# IPv4 local connections:
host    all             all             127.0.0.1/32            trust

or even

# IPv4 local connections:
host    26455462     all             127.0.0.1/32            trust
1 Like
6

you can also create a .pgpass file in the home directory of the user, only accessible to root

7

You may need to specify what DB this user:password should dump. Like:

postgresql://postgres:26455462@127.0.0.1:5432/the_target_db

If pg_hba.conf gets configured properly, and you are logged locally, you could use just (create the_user and give him proper acess) :

postgresql://the_user@localhost/the_target_db

Someone trying to use the_user over network would need password (if extra configs allows it) as the_user is trusted only at local level

# TYPE  DB      USER                 ADDRESS          METHOD
local   all     the_user             localhost        trust
8

Thank you everyone! I appreciate your responses very much.

This command worked (Thx @Rick_Araujo )

pg_dump --dbname=postgresql://postgres:26455462@127.0.0.1:5432/axcys > c:\Temp\TimAxcys122323.backup

The code method is but one method to back up the Db then send the file to our cloud server.

I’m not sure of using pg_hba.conf would be helpful or not. Can anyone explain the benefits?

Thanks again,
Tim

1 Like
9

There you setup your connections to the server (authorization, types, granularity, etc). Read more here:

Using local instead of host (as I told before) will use IPC (UDS) locally avoiding some network (TCP) intercomm attacks via TCP on exposed users, and it’s a little bit faster. Allows more isolation, allows hiding and bypassing passwords as you desired.

There is too much to be understood, the manual link is above. :wink: