trivadis/pgbasenv

pgBasEnv - PostgreSQL Base Environment Tool

pgbasenv's Issues

pgBasEnv sourcing

Hi
In the README.md is written : "File .pgbasenv_profile can be used to source pgBasEnv global environment. It will be added by default to the users ~/.pgsql_profile during installation. At next login pgBasEnv is sourced automatically."

In a fresh installation pgBasEnv is not sourced automatically.
The file ~/.pgsql_profile is created, but it isn't sourced anywhere.

You should change the text in the README.md, or ensure ~/.pgsql_profile is sourced on login.

Regards
Franco

EDB Advanced Server compatibilites

Hi,

should pgbasenv also be capable to scan EDB Advanced Server installations including clusters as well?
As far I could test it doesn't find a EPAS Home at all (installed for e.g. Red Hat derivates at /usr/edb/as14 for version 14).
A created cluster was found by the installation but it somehow doesn't recognized the full status.

LAST_START_HOME and LAST_START will be shown in pgup but the cluster isn't marked a running with STAT=UP, PID, PORT etc.
Maybe it is because there are wrappers or symlinks called like edb-postmaster as a starting process.

Thanks and best regards

Dirk

Sporadic re-writes of the pgclustertab

Hi Trivadis,

In my environment, the pgclustertab is sometimes rewritten. So far I have not been able to identify a pattern of when this happens. So far, this has happened at least twice.

It always comes as a bit of a shock when all PostgreSQL clusters are displayed as DOWN after logging in to the server. The PGDATA directory is found correctly, port and PID are not despite running processes. In addition, new aliases are assigned automatically.

Possibly this happens when after a reboot of the server the PgOperate daemon tries to start up the PostgreSQL clusters, the startup is delayed a bit and I can already log in to the environment / source PgBasEnv. However, I cannot consciously reproduce the behaviour.

As a workaround, I have put aside a copy of the correct pgclustertab and will probably also adjust the permissions of the file so that a change is only possible manually by another user.

Best regards,
Marian

scriptmgr.sh prep not atomic

Hi
the "scriptmgr.sh prep" command is included in .psqlrc and configured by default by the installation. Therefore on a server with multiple Cluster on one host, there could/will be much concurrency on using "psql".
In our env we have disabled the things in .psqlrc, because of this two points:

• as the execution and rewrite of the .run.* are not atomic, you could get an errors like "
  psql:/tools/postgres/local/pgbasenv/scripts/.run.13:3: error: invalid command \ psql:/tools/postgres/local/pgbasenv/scripts/.run.13:3: ERROR: syntax error at or near "." LINE 1: .sql' ^
  as the .run.* file is only half written
• rewriting this files every time psql is executed although nothing changed in almost 100% of the time is in my opinion a bit overkill. I see the problem that it is very comfortable.
  regards
  Cyrill

pgbasenv does not detect installed PostgreSQL software

When installing pgbasenv, the installer does not detect neither the PostgreSQL software nor the installed clusters. It fails with "flock ll: invalid file descriptor" and complains that neither pghometab nor pgclustertab can be found in the directory $TVDBASE/pgbasenv/etc

Next folder(s), timed out during scan. Exclude them or decrease scan depth or increase the timeout in pgbasenv.conf.
Root level folder(s): usr
Failed to load /var/lib/pgsql/tvdtoolbox/pgbasenv/etc/pghometab. File not exist.
Failed to load /var/lib/pgsql/tvdtoolbox/pgbasenv/etc/pgclustertab. File not exist.
/var/lib/pgsql/tvdtoolbox/pgbasenv/bin/pgup.sh: line 244: /var/lib/pgsql/tvdtoolbox/pgbasenv/etc/pgclustertab: No such file or directory

On Debian the .psqlrc runs into an error - :VERSION_NAME is wrong

pascal:/var/lib/postgresql [pgd13]$ psql
psql:/var/lib/postgresql/.psqlrc:5: ERROR: syntax error at or near "("
LINE 1: select substring(''||13.5 (Debian 13.5-0+deb11u1), 1, positi...
^
psql:/var/lib/postgresql/.psqlrc:6: error: /var/lib/postgresql/tvdtoolbox/pgbasenv/scripts/.run.:pgreleasenum: No such file or directory
psql (13.5 (Debian 13.5-0+deb11u1))
Type "help" for help.

pgbasenv shows wrong ssl information

pgbasenv shows self-compiled homes with SSL support even though they don't have SSL support.

Query error in pgsetenv.sh for PostgreSQL version 10

The following query error is shown when pgsetenv.sh is executed for an instance of version 10.x.

Cluster data directories:
┌────────────┬───────┬──────┬───────┬─────────┬───────┬────────────────────────────────────┬──────────────────┬────────────────┐
│ALIAS       │   VER │ STAT │  PORT │     PID │  SIZE │ PGDATA                             │       LAST START │ LAST START HOME│
├────────────┼───────┼──────┼───────┼─────────┼───────┼────────────────────────────────────┼──────────────────┼────────────────┤
│mpdstower01 │    10 │   UP │  5432 │    7549 │   39M │ /u01/app/postgres/mpdstower01/data │ 2021-05-26 21:07 │ /usr/pgsql-10  │
└────────────┴───────┴──────┴───────┴─────────┴───────┴────────────────────────────────────┴──────────────────┴────────────────┘


ERROR:  column "sender_host" does not exist
LINE 1: select 'stb_status:'||sender_host||'|'||sender_port||'|'||st...


---[mpdstower01]:

I think it origins from the following query:

pg01:/var/lib/pgsql/tvdtoolbox/pgbasenv/bin [hr]$ pwd
/var/lib/pgsql/tvdtoolbox/pgbasenv/bin

pg01:/var/lib/pgsql/tvdtoolbox/pgbasenv/bin [hr]$ grep -i sender_host *
pgsetenv.sh:select 'stb_status:'||sender_host||'|'||sender_port||'|'||status from pg_stat_wal_receiver;

Aliases for switching environment are not set, if the alias start with 'db'

Hello,

When setting up pgBasEnv in a new environment, I noticed that the aliases for the clusters for changing the environment were not set, although the informations in $PGBASENV_BASE/etc/pgclustertab were correct and the display of the pgup command also displayed the correct data.

It was possible to define and use the aliases manually, but they were deleted again after usage/change of the environment.

After some tests and comparisons with other environments, I noticed that the aliases were not set (or maybe set - unset) if their name began with "db", e.g. "db123i01". If other alias names were used, they were processed correctly. For example "pg123i01" or even "DB123I01" were processed normally.

Unfortunately I couldn't find the part of the script that causes the problem, but maybe the problem description helps a bit.

Greetings, Marian

PGBASENV_EXCLUDE_FILESYSTEMS

Hi

I don't know if it is a bug or just a configuration thing.
We are running SUSE SLES15SP2 and we have a very slow NFS mount /uxshare on our server.
On every logon we have to wait for pgsetenv.sh to complete, even if in pgbasenv.conf there is the default setting PGBASENV_EXCLUDE_FILESYSTEMS="nfs tmpfs" our slow NFS mount is searched for an installation.

I can see the find process in another terminal during the login of the postgres user:
find /uxshare -maxdepth 7 -type d ( -name bin -o -name global ) ! ( -fstype nfs -o -fstype tmpfs )

As a workaround I can add our NFS mount to PGBASENV_EXCLUDE_DIRS, but is this the intended way?

Regards
Franco

Server marked DOWN

Hi
My Postgres Server is marked DOWN even when it is running.
Using Version 1.2 of pgbasenv.

pgBasEnv v1.2 by Trivadis AG

Installation homes:

ALIAS | VER | OPTIONS | HOME DIR

pgh131 | 13.1 | ssl:1G:8K | /usr/local
pgh131A | 13.1 | ssl:1G:8K | /usr/pgpure/postgres/13

Cluster data directories:

ALIAS | VER | STAT | PORT | PID | SIZE | PGDATA | LAST START | LAST START HOME

PGE01 | 13 | DOWN | 5432 | | 32M | /pgdata/13/PGE01 | 2021-02-04 11:20 | /usr/pgpure/postgres/13

---[PGE01]:

     Installation home: /usr/pgpure/postgres/13
Cluster data directory: /pgdata/13/PGE01
          Cluster port: 5432
        Cluster status: DOWN
       Cluster version: 13

Cluster last start time: 2021-02-04 11:20

---[05.02.2021 17:26]

tdvm0030:/home/postgres [PGE01]$ ps -fu postgres
UID PID PPID C STIME TTY TIME CMD
postgres 9133 1 0 Feb04 ? 00:00:02 /usr/pgpure/postgres/13/bin/postgres -D /etc/pgpure/postgres/13/PGE01
postgres 9135 9133 0 Feb04 ? 00:00:00 postgres: PGE01: checkpointer
postgres 9136 9133 0 Feb04 ? 00:00:00 postgres: PGE01: background writer
postgres 9137 9133 0 Feb04 ? 00:00:00 postgres: PGE01: walwriter
postgres 9138 9133 0 Feb04 ? 00:00:01 postgres: PGE01: autovacuum launcher
postgres 9139 9133 0 Feb04 ? 00:00:02 postgres: PGE01: stats collector
postgres 9140 9133 0 Feb04 ? 00:00:00 postgres: PGE01: logical replication launcher
postgres 18741 1 0 Feb04 ? 00:00:00 /usr/lib/systemd/systemd --user
postgres 18742 18741 0 Feb04 ? 00:00:00 (sd-pam)
postgres 26011 26008 0 17:25 ? 00:00:00 sshd: postgres@pts/0
postgres 26012 26011 0 17:25 pts/0 00:00:00 -bash
postgres 26668 26012 0 17:26 pts/0 00:00:00 ps -fu postgres
tdvm0030:/home/postgres [PGE01]$

lsof warning raised during start of pgbasenv

the following warnings are raised during the start of pgbasenv

lsof: WARNING: can't stat() tracefs file system /sys/kernel/debug/tracing
      Output information may be incomplete.
lsof: WARNING: can't stat() tracefs file system /sys/kernel/debug/tracing
      Output information may be incomplete.
lsof: WARNING: can't stat() tracefs file system /sys/kernel/debug/tracing
      Output information may be incomplete.
lsof: WARNING: can't stat() tracefs file system /sys/kernel/debug/tracing
      Output information may be incomplete.
lsof: WARNING: can't stat() tracefs file system /sys/kernel/debug/tracing
      Output information may be incomplete.
lsof: WARNING: can't stat() tracefs file system /sys/kernel/debug/tracing
      Output information may be incomplete.

the directories are actually excluded with the following settings
PGBASENV_EXCLUDE_DIRS="tmp proc sys tracing debugfs tracefs"

pgBasEnv is not automatically sourced at login for EDBAS

The pgBasEnv installer creates the corresponding files in $HOME that the tool is automatically source at login. In community PostgreSQL the file $HOME/.pgsql_profile is used. EDBAS uses by default the OS user enterprisedb and it expects a file .enterprisedb_profile.

Therefore the installer need to check if the user is enterprisedb and create then .enterprisedb_profile instead of .pgsql_profile.