I noticed that pgbench gets confused about what instance to connect to
when there is both a service *and* an PGPORT given.
I can avoid it rather easily but I report it as it looks like a bug to
me; I can't think of a reason why it might be deliberate behaviour.
Below is a test case where:
instance 9.5 and 9.6 have
database testdb95 and testdb96, and
services '95' and '96'.
$ cat benchservice.sh
# wipe out PG* variables
unset PGSERVICEFILE PGSERVICE PGPORT PGDATA PGHOST PGDATABASE PGPASSFILE
export PGSERVICEFILE=$HOME/.pg_service.conf PGSERVICE=95
PGPASSFILE=$HOME/.pg_aardvark
cat $PGSERVICEFILE
env | grep PG
which pgbench
pgbench --version
# these two calls to pgbench are OK (PGPORT is unset)
pgbench -qis 1 "service=95"
pgbench -qis 2 "service=96"
# just to show that both calls succeeded, and in the right database:
echo 'show port; \dt+ pgbench_accounts' | psql -X "service=95"
echo 'show port; \dt+ pgbench_accounts' | psql -X "service=96"
# now, with PGPORT set to the 9.6 instance, the same pgbench invocation
(with a service) will fail:
export PGPORT=6546
pgbench -qis 1 "service=95"
#--------------------------
And its output:
$ ./benchservice.sh
[96]
port=6546
host=/tmp
user=aardvark
dbname=testdb96
[95]
port=6545
host=/tmp
user=aardvark
dbname=testdb95
PGSERVICEFILE=/home/aardvark/.pg_service.conf
PGSERVICE=95
PGPASSFILE=/home/aardvark/.pg_aardvark
/opt/pgsql95/bin/pgbench
pgbench (PostgreSQL) 9.5.1
creating tables...
100000 of 100000 tuples (100%) done (elapsed 0.11 s, remaining 0.00 s)
vacuum...
set primary keys...
done.
creating tables...
200000 of 200000 tuples (100%) done (elapsed 0.25 s, remaining 0.00 s)
vacuum...
set primary keys...
done.
port
------
6545
(1 row)
List of relations
Schema | Name | Type | Owner | Size | Description
--------+------------------+-------+----------+-------+-------------
public | pgbench_accounts | table | aardvark | 13 MB |
(1 row)
port
------
6546
(1 row)
List of relations
Schema | Name | Type | Owner | Size | Description
--------+------------------+-------+----------+-------+-------------
public | pgbench_accounts | table | aardvark | 26 MB |
(1 row)
connection to database "service=95" failed:
FATAL: database "testdb95" does not exist