Re: Rationale for aversion to the central database?
Maintaining your database logic in version control and versioning the deployed code can be a bit problematic. Conversely, providing a standardized pgsql module through which data is updated and retrieved can help standardize access logic across multiple languages and libraries. And I concur that database portability is a thing people like to discuss, but rarely occurs. Portability is important for general ORM tools, less so for corporate projects (there are always exceptions). Like any tool, needs to be used wisely. I've worked on a project that has 10s of thousands of lines of business logic in the database and it makes perfect sense for that environment. --Ray
Environment Variable for --data-checksum during initdb
I'm trying to remember what environment variable can be set --data-checksum before running postgresql-setup initdb. I know I've seen it but it escapes my recall at the moment. The PostgreSQL documentation has a nice page of environment variables for when PostgreSQL is running, but I'm not finding the variable to set during the initdb phase. Any help to jog my memory greatly appreciated. --Ray
Re: Environment Variable for --data-checksum during initdb
On Thu, Sep 5, 2019 at 3:25 PM Ray Cote wrote: > > I'm trying to remember what environment variable can be set --data-checksum before running postgresql-setup initdb. I know I've seen it but it escapes my recall at the moment. > > The PostgreSQL documentation has a nice page of environment variables for when PostgreSQL is running, but I'm not finding the variable to set during the initdb phase. > > Any help to jog my memory greatly appreciated. > --Ray Found my answer: There are three variables you can set, one for init, one for upgrading, and one for debug messaging. These can be set when calling postgresql-nn-setup. No documentation regarding these in the PostgreSQL documentation. I'm assuming this feature is part of the RHEL/CentOS deploy configuration. Environment: PGSETUP_INITDB_OPTIONS Options carried by this variable are passed to subsequent call of `initdb` binary (see man initdb(1)). This variable is used also during 'upgrade' mode because the new cluster is actually re-initialized from the old one. PGSETUP_PGUPGRADE_OPTIONS Options in this variable are passed next to the subsequent call of `pg_upgrade`. For more info about possible options please look at man pg_upgrade(1). PGSETUP_DEBUG Set to '1' if you want to see debugging output.
Debugging Failed Startup
Does anyone have a hint on how I'd go about debugging why PostgreSQL 11 is not starting on CentOS 7? Was running fine for several weeks then fails to come up after a reboot. I did change the name of the CentOS7 server, but I don't expect that to have any effect on the database. I'm running debug5 to the logs. There's nothing in the ./log directory past the database shutting down. Here's what's in messages. All seems to be fine and then boom! ... postmaster: LOG: listening on IPv6 address "::1", port 5432 ... postmaster: LOG: listening on IPv4 address "127.0.0.1", port 5432 ... postmaster: LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432" ... postmaster: LOG: listening on Unix socket "/tmp/.s.PGSQL.5432" ... postmaster: DEBUG: invoking IpcMemoryCreate(size=148553728) ... postmaster: DEBUG: SlruScanDirectory invoking callback on pg_notify/ ... postmaster: DEBUG: removing file "pg_notify/" ... postmaster: DEBUG: dynamic shared memory system will support 288 segments ... postmaster: DEBUG: created dynamic shared memory control segment 1998722972 (6928 bytes) ... postmaster: DEBUG: max_safe_fds = 983, usable_fds = 1000, already_open = 7 ... postmaster: LOG: redirecting log output to logging collector process ... postmaster: HINT: Future log output will appear in directory "log". ... systemd: postgresql-11.service: main process exited, code=exited, status=1/FAILURE ... systemd: Failed to start PostgreSQL 11 database server. ... systemd: Unit postgresql-11.service entered failed state. --Ray
Re: Debugging Failed Startup
On Thu, May 2, 2019 at 2:57 PM Adrian Klaver wrote: > On 5/2/19 11:22 AM, Ray Cote wrote: > > Does anyone have a hint on how I'd go about debugging why PostgreSQL 11 > > is not starting on CentOS 7? > > Was running fine for several weeks then fails to come up after a reboot. > > > > I did change the name of the CentOS7 server, but I don't expect that to > > have any effect on the database. > > > > I'm running debug5 to the logs. > > The log below is coming from your syslog? > > Is there more in the syslog? > Yes, that's from the syslog (messages) trimmed a little. There's nothing further in the log. Added the debug messages that were prior to that below. Ended up uninstalling and installing and all's fine again. Comes back after a reboot. > > > > There's nothing in the ./log directory past the database shutting down. > > Here's what's in messages. All seems to be fine and then boom! > > > >> ... systemd: Starting PostgreSQL 11 database server... > > ... postmaster: ... DEBUG: postmaster: PostmasterMain: initial >> environment dump: > > ... postmaster: ... DEBUG: - > > ... postmaster: ... DEBUG: #011LANG=en_US.UTF-8 > > ... postmaster: ... DEBUG: >> #011PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin > > ... postmaster: ... DEBUG: #011NOTIFY_SOCKET=/run/systemd/notify > > ... postmaster: ... DEBUG: #011HOME=/var/lib/pgsql > > ... postmaster: ... DEBUG: #011LOGNAME=postgres > > ... postmaster: ... DEBUG: #011USER=postgres > > ... postmaster: ... DEBUG: #011SHELL=/bin/bash > > ... postmaster: ... DEBUG: #011PGDATA=/var/lib/pgsql/11/data/ > > ... postmaster: ... DEBUG: #011PG_OOM_ADJUST_FILE=/proc/self/oom_score_adj > > ... postmaster: ... DEBUG: #011PG_OOM_ADJUST_VALUE=0 > > ... postmaster: ... DEBUG: #011PGLOCALEDIR=/usr/pgsql-11/share/locale > > ... postmaster: ... DEBUG: #011PGSYSCONFDIR=/etc/sysconfig/pgsql > > ... postmaster: ... DEBUG: #011LC_COLLATE=en_US.UTF-8 > > ... postmaster: ... DEBUG: #011LC_CTYPE=en_US.UTF-8 > > ... postmaster: ... DEBUG: #011LC_MESSAGES=en_US.UTF-8 > > ... postmaster: ... DEBUG: #011LC_MONETARY=C > > ... postmaster: ... DEBUG: #011LC_NUMERIC=C > > ... postmaster: ... DEBUG: #011LC_TIME=C > > ... postmaster: ... DEBUG: - > > ... postmaster: ... DEBUG: registering background worker "logical >> replication launcher" > > > ... postmaster: LOG: listening on IPv6 address "::1", port 5432 > > ... postmaster: LOG: listening on IPv4 address "127.0.0.1", port 5432 > > ... postmaster: LOG: listening on Unix socket > > "/var/run/postgresql/.s.PGSQL.5432" > > ... postmaster: LOG: listening on Unix socket "/tmp/.s.PGSQL.5432" > > ... postmaster: DEBUG: invoking IpcMemoryCreate(size=148553728) > > ... postmaster: DEBUG: SlruScanDirectory invoking callback on > > pg_notify/ > > ... postmaster: DEBUG: removing file "pg_notify/" > > ... postmaster: DEBUG: dynamic shared memory system will support 288 > > segments > > ... postmaster: DEBUG: created dynamic shared memory control segment > > 1998722972 (6928 bytes) > > ... postmaster: DEBUG: max_safe_fds = 983, usable_fds = 1000, > > already_open = 7 > > ... postmaster: LOG: redirecting log output to logging collector process > > ... postmaster: HINT: Future log output will appear in directory "log". > > ... systemd: postgresql-11.service: main process exited, code=exited, > > status=1/FAILURE > > ... systemd: Failed to start PostgreSQL 11 database server. > > ... systemd: Unit postgresql-11.service entered failed state. > > > > --Ray > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > -- Raymond Cote, President voice: +1.603.924.6079 email: rgac...@appropriatesolutions.com skype: ray.cote Schedule a meeting: https://calendly.com/ray_cote/60min/
Re: Debugging Failed Startup
Spoke too soon, Not coming back after a boot. Seems I can install, run, but not reboot! On Thu, May 2, 2019 at 3:18 PM Ray Cote wrote: > > > On Thu, May 2, 2019 at 2:57 PM Adrian Klaver > wrote: > >> On 5/2/19 11:22 AM, Ray Cote wrote: >> > Does anyone have a hint on how I'd go about debugging why PostgreSQL 11 >> > is not starting on CentOS 7? >> > Was running fine for several weeks then fails to come up after a reboot. >> > >> > I did change the name of the CentOS7 server, but I don't expect that to >> > have any effect on the database. >> > >> > I'm running debug5 to the logs. >> >> The log below is coming from your syslog? >> >> Is there more in the syslog? >> > Yes, that's from the syslog (messages) trimmed a little. There's nothing > further in the log. > Added the debug messages that were prior to that below. > > Ended up uninstalling and installing and all's fine again. Comes back > after a reboot. > >> > >> > There's nothing in the ./log directory past the database shutting down. >> > Here's what's in messages. All seems to be fine and then boom! >> > >> >>> ... systemd: Starting PostgreSQL 11 database server... >> >> ... postmaster: ... DEBUG: postmaster: PostmasterMain: initial >>> environment dump: >> >> ... postmaster: ... DEBUG: - >> >> ... postmaster: ... DEBUG: #011LANG=en_US.UTF-8 >> >> ... postmaster: ... DEBUG: >>> #011PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin >> >> ... postmaster: ... DEBUG: #011NOTIFY_SOCKET=/run/systemd/notify >> >> ... postmaster: ... DEBUG: #011HOME=/var/lib/pgsql >> >> ... postmaster: ... DEBUG: #011LOGNAME=postgres >> >> ... postmaster: ... DEBUG: #011USER=postgres >> >> ... postmaster: ... DEBUG: #011SHELL=/bin/bash >> >> ... postmaster: ... DEBUG: #011PGDATA=/var/lib/pgsql/11/data/ >> >> ... postmaster: ... DEBUG: >>> #011PG_OOM_ADJUST_FILE=/proc/self/oom_score_adj >> >> ... postmaster: ... DEBUG: #011PG_OOM_ADJUST_VALUE=0 >> >> ... postmaster: ... DEBUG: #011PGLOCALEDIR=/usr/pgsql-11/share/locale >> >> ... postmaster: ... DEBUG: #011PGSYSCONFDIR=/etc/sysconfig/pgsql >> >> ... postmaster: ... DEBUG: #011LC_COLLATE=en_US.UTF-8 >> >> ... postmaster: ... DEBUG: #011LC_CTYPE=en_US.UTF-8 >> >> ... postmaster: ... DEBUG: #011LC_MESSAGES=en_US.UTF-8 >> >> ... postmaster: ... DEBUG: #011LC_MONETARY=C >> >> ... postmaster: ... DEBUG: #011LC_NUMERIC=C >> >> ... postmaster: ... DEBUG: #011LC_TIME=C >> >> ... postmaster: ... DEBUG: - >> >> ... postmaster: ... DEBUG: registering background worker "logical >>> replication launcher" >> >> > ... postmaster: LOG: listening on IPv6 address "::1", port 5432 >> > ... postmaster: LOG: listening on IPv4 address "127.0.0.1", port 5432 >> > ... postmaster: LOG: listening on Unix socket >> > "/var/run/postgresql/.s.PGSQL.5432" >> > ... postmaster: LOG: listening on Unix socket "/tmp/.s.PGSQL.5432" >> > ... postmaster: DEBUG: invoking IpcMemoryCreate(size=148553728) >> > ... postmaster: DEBUG: SlruScanDirectory invoking callback on >> > pg_notify/ >> > ... postmaster: DEBUG: removing file "pg_notify/" >> > ... postmaster: DEBUG: dynamic shared memory system will support 288 >> > segments >> > ... postmaster: DEBUG: created dynamic shared memory control segment >> > 1998722972 (6928 bytes) >> > ... postmaster: DEBUG: max_safe_fds = 983, usable_fds = 1000, >> > already_open = 7 >> > ... postmaster: LOG: redirecting log output to logging collector >> process >> > ... postmaster: HINT: Future log output will appear in directory "log". >> > ... systemd: postgresql-11.service: main process exited, code=exited, >> > status=1/FAILURE >> > ... systemd: Failed to start PostgreSQL 11 database server. >> > ... systemd: Unit postgresql-11.service entered failed state. >> > >> > --Ray >> >> >> -- >> Adrian Klaver >> adrian.kla...@aklaver.com >> > > > -- > Raymond Cote, President > voice: +1.603.924.6079 email: rgac...@appropriatesolutions.com skype: > ray.cote > Schedule a meeting: https://calendly.com/ray_cote/60min/ > > > -- Raymond Cote, President voice: +1.603.924.6079 email: rgac...@appropriatesolutions.com skype: ray.cote Schedule a meeting: https://calendly.com/ray_cote/60min/
Re: Debugging Failed Startup
On Thu, May 2, 2019 at 4:12 PM Alvaro Herrera wrote: > On 2019-May-02, Ray Cote wrote: > > > Does anyone have a hint on how I'd go about debugging why PostgreSQL 11 > is > > not starting on CentOS 7? > > Was running fine for several weeks then fails to come up after a reboot. > > How are you getting it started after the reboot? If you're not using > systemd facilities (eg. if you're calling pg_ctl on your own script), > systemd may be getting confused about what's the status of the service > and stopping it on its own. systemctl start postgresql-11 Plus I've run systemctl enable postgresql-11 > Also, what are the timestamp differences between the final "redirecting > log output" line and the "Failed to start" lines? > 0 second difference. Not sure what's happend. Re-installed a second time. Now it has survived pass three reboots and seems happy. Was just odd that there wasn't any error message as to why it failed. As an aside to the person asking about changing back the hostname. I re-installed using the new hostname and it still failed once. Thanks for everyone's feedback. Seems to be happy again. --Ray > > > -- > Álvaro Herrerahttps://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services > -- Raymond Cote, President voice: +1.603.924.6079 email: rgac...@appropriatesolutions.com skype: ray.cote Schedule a meeting: https://calendly.com/ray_cote/60min/
