Postgres + Perl + Ubuntu

anonymized user 468275 has asked for the wisdom of the Perl Monks concerning the following question:

Hi, I have a puzzling issue when trying to migrate a database. The database was previously set up under Postgres 9.5.3 on Windows 10 using activeperl latest version with DBD:Pg.

The target environment for the migration is Ubuntu 14.04. The latest version of Postgres for this appears to be 9.3. The Perl version it has is 5.18.2.

After apt-get install of Postgres, defaulting to 9.3, PgAdminIII, cpan installation of DBI, DateTime and DBD:Pg, I created the databases with the same names, roles and passwords as the Windows installation, using pgsql. I can log on from pgsql, but neither the postgres user or the roles can log in from DBI, behaving as if the username and password are wrong, but I am sure they are not. I get the same kind of problem from PgAdminIII, so it isn't just Perl but PgAdminIII suffering the same problem. I checked the Postgres configuration files and there is nothing wrong there. It isn't a network problem because all access methods above were local. I checked for having more than one installed version of Postgres, which would explain it, but no, there is only one. So the problem appears to lie under the hood somewhere, not obviously in DBI otherwise pgadmin would work, but I am not sure where to look next. All suggestions are welcome! Thanks!

One world, one people

Comment on Postgres + Perl + Ubuntu

Replies are listed 'Best First'.
Re: Postgres + Perl + Ubuntu by marto (Cardinal) on Jun 21, 2016 at 08:28 UTC
Show us how you're connecting. This may be different than on Windows, e.g. host	[reply]
Re^2: Postgres + Perl + Ubuntu by anonymized user 468275 (Curate) on Jun 21, 2016 at 10:57 UTC
OK, it wasn't host, but it was indeed the connect options as you suspected. I'll post what it was as a reply to my OP. One world, one people	[reply]
Re^2: Postgres + Perl + Ubuntu by anonymized user 468275 (Curate) on Jun 21, 2016 at 08:55 UTC
host is left empty, but you could be on to something there, because the method of connecting to localhost is very different from windows. So DBI could be looking in the wrong directory for postgres config. I'll look into it further, thanks!! For the whole picture: only the dbname and password are specified, the rest of the connect is left to default. One world, one people	[reply]
Re: Postgres + Perl + Ubuntu by anonymized user 468275 (Curate) on Jun 21, 2016 at 11:12 UTC
Marto put me on the right track. I was using minimal parameters according to DBI, but TOO minimal for DBD:Pg. Under 9.5.3 and activeperl 5.22 on Win10, it defaulted the final parameter of connect to {autocommit=>0}. Under 9.3 and perl 5.18 on Ubuntu, it got confused by the omission of the final parameter and just raised its default connect failure. So surprisingly enough, the logon problem was fixed by explicitly adding the {autocommit=>0}. I had missed it but got away with it for Windows but not for the new target Ubuntu platform (where it will eventually get hosted as a web service). One world, one people	[reply]
Re: Postgres + Perl + Ubuntu by ikegami (Patriarch) on Jun 21, 2016 at 16:07 UTC
Connecting via a unix socket vs connecting via the local interface (127.0.0.1) vs connection via a non-local interface can make a difference. Check to which one the server listens, and check the user permissions to see which user can be used on which interface. Adjust as needed.	[reply]
Re: Postgres + Perl + Ubuntu by hippo (Archbishop) on Jun 21, 2016 at 10:11 UTC
So the problem appears to lie under the hood somewhere, not obviously in DBI otherwise pgadmin would work, but I am not sure where to look next. Look in the postgresql log (the one in `pg_log`) - this will inform you of the reasons for denying login.	[reply] [d/l]
Re: Postgres + Perl + Ubuntu by Mr. Muskrat (Canon) on Jun 21, 2016 at 14:52 UTC
You don't have to use 9.3. Check out the PostgreSQL downloads page for Ubuntu: https://www.postgresql.org/download/linux/ubuntu/. You can get 9.4 from the apt repo or get 9.5 by building from source (which is not as hard as it sounds). (update: grammar)	[reply]
Re: Postgres + Perl + Ubuntu by Anonymous Monk on Jun 21, 2016 at 07:42 UTC
postgres forum? yeah I know, too obvious and you probably already did it :)	[reply]
Re^2: Postgres + Perl + Ubuntu by anonymized user 468275 (Curate) on Jun 21, 2016 at 07:55 UTC
Actually the reason I am posting here is that I want the connection from DBD:Pg to work. With Perl I can expect to be able to step through with the debugger and check things to fix, and everything works fine from psql. I don't need to fix pgadmin and anyway it's a black box, well, at least relatively speaking, compared with Perl. One world, one people	[reply]