UNIX domain socketでpostgresqlにアクセスする

postgresqlの認証周りも含めると面倒なのでpostgresユーザーでアクセス。

// dockerコンテナでpostgresqlを構築versionはCentOS標準の9.2
$ docker run --privileged=true -d --name testpg -v `pwd`/pg:/run/uds -i -t docker.io/centos:7.4.1708  /sbin/init
$ docker exec -i -t testpg /bin/bash
# yum install -y postgresql-server perl-DBD-Pg bash-completion

// bash-completion便利なので入れておく
# source /root/.bashrc

// initdbはこう呼ぶらしい
# export PGSETUP_INITDB_OPTIONS="--encoding=UTF-8 --locale=C"
# postgresql-setup initdb

# systemctl start postgresql.service
# su - postgres
$ createdb company
$ psql company -c "CREATE TABLE employee (id serial primary key, name text);"
$ psql company -c "INSERT INTO employee (name) VALUES ('社長')"
$ psql company -c "INSERT INTO employee (name) VALUES ('秘書')"
$ psql company -c "INSERT INTO employee (name) VALUES ('愛人')"
$ psql company -c "SELECT * FROM employee"
 id |  name
----+--------
  1 | 社長
  2 | 秘書
  3 | 愛人
(3 rows)

$ cat a.pl
use v5.10;
use warnings;
use DBI;
use Data::Dumper;

my $dbh = DBI->connect("dbi:Pg:host=/var/run/postgresql;dbname=company");
my $sth = $dbh->prepare("SELECT id,name FROM employee");
$sth->execute();
while( my $aryref = $sth->fetch) {
        my ($id, $name) = @$aryref;
        say "$id $name";
}
$dbh->disconnect;
$ perl a.pl
1 社長
2 秘書
3 愛人

おまけ

ソケットファイルの場所を/tmpから変えるには/var/lib/pgsql/data/postgresql.confのunix_socket_directoriesのように変える
unix_socket_directories = '/run/uds'
unix_socket_directoryは×

参考

Perl - Connect to PostgreSQL Database - Failing to find proper socket - Stack Overflow
https://stackoverflow.com/questions/16427356/perl-connect-to-postgresql-database-failing-to-find-proper-socket

sql - How to set auto increment primary key in PostgreSQL? - Stack Overflow
https://stackoverflow.com/questions/7718585/how-to-set-auto-increment-primary-key-in-postgresql

DBD::Pg - search.cpan.org
http://search.cpan.org/~turnstep/DBD-Pg/Pg.pm