postgres is all kinds of broken

[richlowe]

the -client and -server packages seem to not have installed the SMF serivecs.

cronfa:~> tail /var/db/pkgin/pkg_install-err.log
---Sep 06 23:02:45: installing postgresql94-docs-9.4.6...
---Sep 06 23:02:47: installing postgresql94-9.4.6...
---Sep 06 23:32:12: installing xmlcatmgr-2.2nb1...
---Sep 06 23:32:14: installing libxml2-2.9.4...
---Sep 06 23:32:15: installing ossp-uuid-1.6.2nb5...
---Sep 06 23:32:15: installing postgresql94-contrib-9.4.6...
---Sep 06 23:32:17: installing postgresql94-datatypes-9.4.6...
pkg_add: Conflicting PLIST with postgresql94-contrib-9.4.6: lib/postgresql/btree_gist.so
pkg_add: 1 package addition failed

cronfa:~> sudo -u postgres initdb /data/postgresql
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /data/postgresql ... ok
creating subdirectories ... ok
selecting default max_connections ... 10
selecting default shared_buffers ... 400kB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
creating template1 database in /data/postgresql/base/1 ... FATAL:  could not open directory "/opt/local/share/postgresql/timezonesets": No such file or directory
HINT:  This may indicate an incomplete PostgreSQL installation, or that the file "/opt/local/bin/postgres" has been moved away from its proper location.
child process exited with exit code 1
initdb: removing contents of data directory "/data/postgresql"

[richlowe]

@rmustacc asked I include the image version, it is:

e1faace4-e19b-11e5-928b-83849e2fd94a  minimal-64-lts         15.4.1
cronfa:~> cat /etc/pkgsrc_version
release: 2015Q4
architecture: x86_64

[richlowe]

@nshalman suggested I uninstall postgresql94-* and install them again. This, fixed all 3(?) of these issues. Which is equally surprising and concerning.

For the record, what I did initially is install postgresql94-client and postgresql94-server, which left me in the very broken state of this issue, I then tried to add the other packages (hoping they provided what I was missing), and hit the PLIST conflict.

Then I uninstalled all this stuff, then installed it again and... everything is now ok.

[nshalman]

Also for the record, I was unable to reproduce in a fresh zone:

[root@smartos /opt/vmconfigs]# tee /dev/stderr < richlowe.json | vmadm create
{
  "alias": "richlowe",
  "hostname": "richlowe",
  "brand": "joyent",
  "max_physical_memory": 2048,
  "image_uuid": "e1faace4-e19b-11e5-928b-83849e2fd94a",
  "resolvers": ["8.8.8.8","8.8.4.4"],
  "nics": [
    {
      "nic_tag": "external",
      "ips": ["dhcp"],
      "primary": "1"
    }
  ]
}
Successfully created VM b987e9e9-ef8e-4e94-dce3-8950e8b858c3
[root@smartos /opt/vmconfigs]# zlogin b987e9e9-ef8e-4e94-dce3-8950e8b858c3
[Connected to zone 'b987e9e9-ef8e-4e94-dce3-8950e8b858c3' pts/2]
   __        .                   .
 _|  |_      | .-. .  . .-. :--. |-
|_    _|     ;|   ||  |(.-' |  | |
  |__|   `--'  `-' `;-| `-' '  ' `-'
                   /  ; Instance (minimal-64-lts 15.4.1)
                   `-'  https://docs.joyent.com/images/smartos/minimal

[root@richlowe ~]# pkgin -y install postgresql94-client postgresql94-server ; svcs -a | grep postgres ; logout
reading local summary...
processing local summary...
pkg_summary.xz                      100% 1998KB 999.0KB/s 526.1KB/s   00:02
calculating dependencies... done.

nothing to upgrade.
6 packages to be installed (83M to download, 268M to install):

db4-4.8.30 tcp_wrappers-7.6.4 cyrus-sasl-2.1.26nb4 openldap-client-2.4.44 postgresql94-client-9.4.6 postgresql94-server-9.4.6

downloading packages...
db4-4.8.30.tgz                      100%   59MB   8.4MB/s   6.8MB/s   00:07
tcp_wrappers-7.6.4.tgz              100%  246KB 245.9KB/s 245.9KB/s   00:01
cyrus-sasl-2.1.26nb4.tgz            100%  940KB 939.6KB/s 939.6KB/s   00:00
openldap-client-2.4.44.tgz          100% 3468KB   3.4MB/s   3.4MB/s   00:00
postgresql94-client-9.4.6.tgz       100% 5909KB   5.8MB/s   5.8MB/s   00:01
postgresql94-server-9.4.6.tgz       100%   14MB   6.9MB/s   7.4MB/s   00:02
installing packages...
installing db4-4.8.30...
installing tcp_wrappers-7.6.4...
installing cyrus-sasl-2.1.26nb4...
cyrus-sasl-2.1.26nb4: Creating user ``cyrus''
passwd: password information changed for cyrus
===========================================================================
$NetBSD: MESSAGE,v 1.3 2011/09/17 11:32:02 obache Exp $

cyrus-sasl-2.1.26nb4 by default does *not* install any authentication plugin
modules.  Until the necessary authentication plugin modules are
installed, e.g. security/cy2-crammd5 or security/cy2-gssapi, you
will probably get errors about "no appropriate mechs available".

cyrus-sasl-2.1.26nb4 will use the following directory as the default
path to its plugin modules:

        /opt/local/lib/sasl2

The search path for plugin modules can be customized by setting the
SASL_PATH variable to a colon-delimted list of directories in the shell
environment.

To allow plaintext authentication without using the auxprop mechanism, the
package security/cyrus-saslauthd should be installed.

===========================================================================
installing openldap-client-2.4.44...
openldap-client-2.4.44: copying /opt/local/share/examples/openldap/ldap.conf to /opt/local/etc/openldap/ldap.conf
installing postgresql94-client-9.4.6...
postgresql94-client-9.4.6: copying /opt/local/share/postgresql/pg_service.conf.sample to /opt/local/etc/postgresql/pg_service.conf
postgresql94-client-9.4.6: copying /opt/local/share/postgresql/psqlrc.sample to /opt/local/etc/postgresql/psqlrc
installing postgresql94-server-9.4.6...
postgresql94-server-9.4.6: Creating group ``postgres''
postgresql94-server-9.4.6: Creating user ``postgres''
passwd: password information changed for postgres

Initializing the PostgreSQL database cluster.

The PostgreSQL database cluster has been initialized.

The default password for the master 'postgres' user is:

  postgres

===========================================================================
This package has SMF support.  You may use svcadm(1M) to 'enable', 'disable'
or 'restart' services.  To enable the instance(s) for this package, run:

        /usr/sbin/svcadm enable -r svc:/pkgsrc/postgresql:default

Use svcs(1) to check on service status.  See smf(5) for more information.
===========================================================================
===========================================================================
$NetBSD: MESSAGE,v 1.1 2015/01/14 21:01:19 adam Exp $

To enable this package, put the following into your /etc/rc.conf:

        pgsql=YES
        pgsql_flags="-l"     # enables SSL connections (TCP/IP and
                     # ~pgsql/data/server.crt required)

and ensure that

        /etc/rc.d/pgsql start

is run at the appropriate time.

Note: PostgreSQL accepts TCP/IP connections on localhost address by default.

===========================================================================
pkg_install warnings: 0, errors: 0
reading local summary...
processing local summary...
marking postgresql94-client-9.4.6 as non auto-removable
marking postgresql94-server-9.4.6 as non auto-removable
disabled        0:07:49 svc:/pkgsrc/postgresql:default

[Connection to zone 'b987e9e9-ef8e-4e94-dce3-8950e8b858c3' pts/2 closed]
[root@smartos /opt/vmconfigs]# vmadm delete b987e9e9-ef8e-4e94-dce3-8950e8b858c3
Successfully deleted VM b987e9e9-ef8e-4e94-dce3-8950e8b858c3
[root@smartos /opt/vmconfigs]# uname -a
SunOS smartos 5.11 joyent_20160901T054050Z i86pc i386 i86pc

[mamash]

This line suggests that postgresql94-server failed to install all files (or lost them after installation for whatever reasons):

creating template1 database in /data/postgresql/base/1 ... FATAL:  could not open directory "/opt/local/share/postgresql/timezonesets": No such file or directory

Anything in /var/db/pkgin/pkg_install-err.log above the snippet you pasted that would relate to the server package?

[mamash]

I'm resolving the conflict of -contrib and -datatypes (BTW the -contrib package has been entirely reworked starting with 2016Q2 and behaves now more or less as the Linux contrib packages). However, we don't see how that would affect the -server package installation.

[mamash]

I removed the conflict as part of bumping PostgreSQL versions in 2015Q4 last night. Let me know if you managed to recover any more details from the pkg_install-err.log file.

[mamash]

Closing this issue because we didn't find a way to reproduce. Contact let us if you have any further information on this.