Thread: Crash: backup / restore
PGAdmin hangs when dumping / restoring a database. I can get to the screen that has the pg_dump / pg_restore command, but after that screen is displayed there is no progress, and only thing I can do is kill the program. (the file dumped to is created but it is empty). I thing the bug is related to my pg_hba.conf, relevant lines: local all all trust host all all 127.0.0.1/32 trust host all all xxx.zzz.0.0/16 md5 I use user "akaariai" which has no password and which is both database superuser and my local linux user. My PGAdmin connection is set up as follows: name: localhost host: localhost port: 5432 maintenance_db: postgres username: akaariai store password checked, restore_env checked. Nothing else set, that means also no password set. I can open the connection without any errors. The command PGAdmin is trying to run when backup is requested: pg_dump --host localhost --port 5432 --username akaariai --format ... If I run the command manually, the command asks for password. If I run the command without "--host localhost", there is no password prompt. Could it be that PGAdmin doesn't expect the server to ask password? Also, if I remove the host=localhost from pgadmin connection configuration (leaving host blank), I can backup any DB without errors. I am using ubuntu 10.04, apt installed postgresql 8.4.5, apt installed pgAdmin, version: 1.10.2 rev 8217. - Anssi
Le 19/10/2010 13:24, Anssi Kääriäinen a écrit : > PGAdmin hangs when dumping / restoring a database. I can get to the > screen that has the pg_dump / pg_restore command, but after that screen > is displayed there is no progress, and only thing I can do is kill the > program. (the file dumped to is created but it is empty). > > I thing the bug is related to my pg_hba.conf, relevant lines: > > local all all trust > host all all 127.0.0.1/32 trust > host all all xxx.zzz.0.0/16 md5 > > I use user "akaariai" which has no password and which is both database > superuser and my local linux user. > > My PGAdmin connection is set up as follows: > name: localhost > host: localhost > port: 5432 > maintenance_db: postgres > username: akaariai > store password checked, restore_env checked. Nothing else set, that > means also no password set. I can open the connection without any errors. > > The command PGAdmin is trying to run when backup is requested: > pg_dump --host localhost --port 5432 --username akaariai --format ... > > If I run the command manually, the command asks for password. If I run > the command without "--host localhost", there is no password prompt. > Could it be that PGAdmin doesn't expect the server to ask password? > There is no communication between pgAdmin and pg_dump. If pg_dump needs to ask for a password, you'll surely have your pgAdmin completely blocked. > Also, if I remove the host=localhost from pgadmin connection > configuration (leaving host blank), I can backup any DB without errors. > From pgAdmin, I suppose? > I am using ubuntu 10.04, apt installed postgresql 8.4.5, apt installed > pgAdmin, version: 1.10.2 rev 8217. > pgAdmin cannot give the password to pg_dump without using the environment variable (PGPASSWORD)... but it means the password will appear in the ps output. That won't happen. The only workaround available is to use a .pgpass file. Which pgAdmin would have created if you allowed it to store the password. -- Guillaumehttp://www.postgresql.frhttp://dalibo.com
On 10/19/2010 09:14 PM, Guillaume Lelarge wrote: >> Also, if I remove the host=localhost from pgadmin connection >> configuration (leaving host blank), I can backup any DB without errors. >> >> > From pgAdmin, I suppose? > Yes, from the servers left click a server, pick properties, and remove all text from Host portion. > >> I am using ubuntu 10.04, apt installed postgresql 8.4.5, apt installed >> pgAdmin, version: 1.10.2 rev 8217. >> >> > pgAdmin cannot give the password to pg_dump without using the > environment variable (PGPASSWORD)... but it means the password will > appear in the ps output. That won't happen. The only workaround > available is to use a .pgpass file. Which pgAdmin would have created if > you allowed it to store the password. > > The point is there is no postgresql password for the user. A little more about why the problem happens: Pgadmin allows me to connect without any password to server when I have host=localhost in server configuration. If I try to connect from command line with psql: psql -h localhost -p 5432 -d xxx -U yyy I get password prompt. If I do not specify -h and -p I will not get password prompt, and if I issue psql -h 127.0.0.1 -p 5432 -d xxx -U yyy I will not get password prompt. resolveip localhost gives me: IP address of localhost is 127.0.0.1 IP address of localhost is 127.0.0.1 (yes, two lines) So when connecting to localhost from pgadmin I do not need password. When pgadmin issues the dump command, it uses -h localhost -p 5432, and thus pg_dump requires a password. I can fix this with leaving the host blank in server configuration. If I have 127.0.0.1 as host, for some reason, pgadmin requires me to supply a password. This is opposite to what psql does. Anyway, the bug is that I can connect without password when host=localhost, but pgadmin hangs when trying to backup, because "pg_dump -h localhost ..." requires a password. - Anssi
Le 21/10/2010 01:36, Anssi Kääriäinen a écrit : > On 10/19/2010 09:14 PM, Guillaume Lelarge wrote: >>> Also, if I remove the host=localhost from pgadmin connection >>> configuration (leaving host blank), I can backup any DB without errors. >>> >>> >> From pgAdmin, I suppose? >> > Yes, from the servers left click a server, pick properties, and remove > all text from Host portion. >> >>> I am using ubuntu 10.04, apt installed postgresql 8.4.5, apt installed >>> pgAdmin, version: 1.10.2 rev 8217. >>> >>> >> pgAdmin cannot give the password to pg_dump without using the >> environment variable (PGPASSWORD)... but it means the password will >> appear in the ps output. That won't happen. The only workaround >> available is to use a .pgpass file. Which pgAdmin would have created if >> you allowed it to store the password. >> >> > The point is there is no postgresql password for the user. A little more > about why the problem happens: > > Pgadmin allows me to connect without any password to server when I have > host=localhost in server configuration. If I try to connect from command > line with psql: > psql -h localhost -p 5432 -d xxx -U yyy > I get password prompt. If I do not specify -h and -p I will not get > password prompt, and if I issue > psql -h 127.0.0.1 -p 5432 -d xxx -U yyy > I will not get password prompt. resolveip localhost gives me: > IP address of localhost is 127.0.0.1 > IP address of localhost is 127.0.0.1 > (yes, two lines) > > So when connecting to localhost from pgadmin I do not need password. > When pgadmin issues the dump command, it uses -h localhost -p 5432, and > thus pg_dump requires a password. I can fix this with leaving the host > blank in server configuration. If I have 127.0.0.1 as host, for some > reason, pgadmin requires me to supply a password. This is opposite to > what psql does. > > Anyway, the bug is that I can connect without password when > host=localhost, but pgadmin hangs when trying to backup, because > "pg_dump -h localhost ..." requires a password. > The only reason I could guess is that localhost is resolved differently, once in IPv4 and once in IPv6. And that either your pg_hba.conf accepts IPv4 and IPv6 connections but with a different authentication methode, or your .pgpass already has the password for IPv4, and not IPv6 (or vice-versa). Not sure I explained myself clearly :) I think you should check your .pgpass file and your pg_hba.conf file. And be careful about IPv4 and IPv6 resolution. -- Guillaumehttp://www.postgresql.frhttp://dalibo.com
On 10/22/2010 02:06 AM, Guillaume Lelarge wrote: > The only reason I could guess is that localhost is resolved differently, > once in IPv4 and once in IPv6. And that either your pg_hba.conf accepts > IPv4 and IPv6 connections but with a different authentication methode, > or your .pgpass already has the password for IPv4, and not IPv6 (or > vice-versa). Not sure I explained myself clearly :) > > I think you should check your .pgpass file and your pg_hba.conf file. > And be careful about IPv4 and IPv6 resolution. > Yes, this is it: I had this leftover line in pg_hba.conf: host all all ::1/128 md5 Removing the line gives me: FATAL: no pg_hba.conf entry for host "::1", user "akaariai", ... And changing md5 to trust fixes the issue. I wonder if it would be possible to supply -w to pg_dump to make pg_dump fail automatically instead of asking for password and thus hanging the whole pgadmin program. Unfortunately supported only from version 8.4... - Anssi
Le 25/10/2010 00:43, Anssi Kääriäinen a écrit : > On 10/22/2010 02:06 AM, Guillaume Lelarge wrote: >> The only reason I could guess is that localhost is resolved differently, >> once in IPv4 and once in IPv6. And that either your pg_hba.conf accepts >> IPv4 and IPv6 connections but with a different authentication methode, >> or your .pgpass already has the password for IPv4, and not IPv6 (or >> vice-versa). Not sure I explained myself clearly :) >> >> I think you should check your .pgpass file and your pg_hba.conf file. >> And be careful about IPv4 and IPv6 resolution. >> > Yes, this is it: > I had this leftover line in pg_hba.conf: > host all all ::1/128 md5 > > Removing the line gives me: > FATAL: no pg_hba.conf entry for host "::1", user "akaariai", ... > > And changing md5 to trust fixes the issue. > > I wonder if it would be possible to supply -w to pg_dump to make pg_dump > fail automatically instead of asking for password and thus hanging the > whole pgadmin program. Unfortunately supported only from version 8.4... > This is actually a great idea. I'll try to add this for next release. You're right that it's only supported from 8.4, but I still think this is something we should do. -- Guillaumehttp://www.postgresql.frhttp://dalibo.com
Le 25/10/2010 08:04, Guillaume Lelarge a écrit : > Le 25/10/2010 00:43, Anssi Kääriäinen a écrit : >> On 10/22/2010 02:06 AM, Guillaume Lelarge wrote: >>> The only reason I could guess is that localhost is resolved differently, >>> once in IPv4 and once in IPv6. And that either your pg_hba.conf accepts >>> IPv4 and IPv6 connections but with a different authentication methode, >>> or your .pgpass already has the password for IPv4, and not IPv6 (or >>> vice-versa). Not sure I explained myself clearly :) >>> >>> I think you should check your .pgpass file and your pg_hba.conf file. >>> And be careful about IPv4 and IPv6 resolution. >>> >> Yes, this is it: >> I had this leftover line in pg_hba.conf: >> host all all ::1/128 md5 >> >> Removing the line gives me: >> FATAL: no pg_hba.conf entry for host "::1", user "akaariai", ... >> >> And changing md5 to trust fixes the issue. >> >> I wonder if it would be possible to supply -w to pg_dump to make pg_dump >> fail automatically instead of asking for password and thus hanging the >> whole pgadmin program. Unfortunately supported only from version 8.4... >> > > This is actually a great idea. I'll try to add this for next release. > You're right that it's only supported from 8.4, but I still think this > is something we should do. > OK, I have a patch that implements this. I'll commit it in a few days if there is no objection. -- Guillaume http://www.postgresql.fr http://dalibo.com
Le 26/10/2010 07:52, Guillaume Lelarge a écrit : > Le 25/10/2010 08:04, Guillaume Lelarge a écrit : >> Le 25/10/2010 00:43, Anssi Kääriäinen a écrit : >>> On 10/22/2010 02:06 AM, Guillaume Lelarge wrote: >>>> The only reason I could guess is that localhost is resolved differently, >>>> once in IPv4 and once in IPv6. And that either your pg_hba.conf accepts >>>> IPv4 and IPv6 connections but with a different authentication methode, >>>> or your .pgpass already has the password for IPv4, and not IPv6 (or >>>> vice-versa). Not sure I explained myself clearly :) >>>> >>>> I think you should check your .pgpass file and your pg_hba.conf file. >>>> And be careful about IPv4 and IPv6 resolution. >>>> >>> Yes, this is it: >>> I had this leftover line in pg_hba.conf: >>> host all all ::1/128 md5 >>> >>> Removing the line gives me: >>> FATAL: no pg_hba.conf entry for host "::1", user "akaariai", ... >>> >>> And changing md5 to trust fixes the issue. >>> >>> I wonder if it would be possible to supply -w to pg_dump to make pg_dump >>> fail automatically instead of asking for password and thus hanging the >>> whole pgadmin program. Unfortunately supported only from version 8.4... >>> >> >> This is actually a great idea. I'll try to add this for next release. >> You're right that it's only supported from 8.4, but I still think this >> is something we should do. >> > > OK, I have a patch that implements this. I'll commit it in a few days if > there is no objection. > Commited. Thanks for your idea about -v switch. -- Guillaumehttp://www.postgresql.frhttp://dalibo.com