Upgrade error backing up Postgre upgrade to version 5.3.4"

[valmberg]

On attempting the upgrade to Nagios XI 5.3.4 (from 5.3.3), getting the following:

Code: Select all

Backing up PostgresQL databases...
pg_dump: SQL command failed
pg_dump: Error message from server: ERROR:  permission denied for relation xi_eventqueue
pg_dump: The command was: LOCK TABLE public.xi_eventqueue IN ACCESS SHARE MODE
Error backing up PostgresQL database 'nagiosxi' !

Here is the full Nagios XI Backup log:

Code: Select all

---- Starting Nagios XI Backup ----
No entry for terminal type "unknown";
using dumb terminal settings.
Running configuration check...
Stopping nagios:. done.
Starting nagios: done.
Backing up Core Config Manager (NagiosQL)...
tar: Removing leading `/' from member names
tar: Removing leading `/' from member names
Backing up Nagios Core...
tar: Removing leading `/' from member names
tar: /usr/local/nagios/var/rw/nagios.qh: socket ignored
tar: /usr/local/nagios/var/ndo.sock: socket ignored
tar: /usr/local/nagios/var: file changed as we read it
tar: /usr/local/nagios/share/perfdata/Eassfsprd1.oya.ad: file changed as we read it
tar: /usr/local/nagios/share/perfdata/MacLaren_Router/_HOST_.xml: file changed as we read it
tar: /usr/local/nagios/share/perfdata/MacLaren_Router: file changed as we read it
Backing up Nagios XI...
tar: Removing leading `/' from member names
Backing up MRTG...
tar: Removing leading `/' from member names
Backing up NRDP...
tar: Removing leading `/' from member names
Backing up Nagvis...
tar: Removing leading `/' from member names
Backing up MySQL databases...
Backing up PostgresQL databases...
pg_dump: SQL command failed
pg_dump: Error message from server: ERROR:  permission denied for relation xi_eventqueue
pg_dump: The command was: LOCK TABLE public.xi_eventqueue IN ACCESS SHARE MODE
Error backing up PostgresQL database 'nagiosxi' !

[avandemore]

Have you made any changes to postgresql? By default, that user has permissions to dump the database.

[valmberg]

Negative. I have not made any cuustom changes to postgresql.

I did have a help request in for some issues that I had a couple of weeks ago. We ran some SQL commands.

You can see what we ran in RE: Ticket#2016120110000187

Here is the last command we ran:

# psql -U postgres nagiosxi
ALTER TABLE xi_users ADD COLUMN api_key character varying(128);
ALTER TABLE xi_users ADD COLUMN api_enabled smallint DEFAULT 0::smallint NOT NULL;

UPDATE xi_users SET api_enabled = 1, api_key = backend_ticket;

-- Account security features
ALTER TABLE xi_users ADD COLUMN login_attempts smallint DEFAULT 0::smallint NOT NULL;
ALTER TABLE xi_users ADD COLUMN last_attempt integer DEFAULT 0::integer NOT NUll;
ALTER TABLE xi_users ADD COLUMN last_password_change integer DEFAULT 0::integer NOT NULL;

-- Security information
ALTER TABLE xi_users ADD COLUMN last_login integer DEFAULT 0::integer NOT NUll;
ALTER TABLE xi_users ADD COLUMN last_edited integer DEFAULT 0::integer NOT NUll;
ALTER TABLE xi_users ADD COLUMN last_edited_by integer DEFAULT 0::integer NOT NUll;
ALTER TABLE xi_users ADD COLUMN created_by integer DEFAULT 0::integer NOT NUll;
ALTER TABLE xi_users ADD COLUMN created_time integer DEFAULT 0::integer NOT NUll;

-- Event queue sequence
CREATE SEQUENCE xi_eventqueue_eventqueue_id_seq
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;

-- Event queue table
CREATE TABLE xi_eventqueue (
eventqueue_id integer DEFAULT nextval('xi_eventqueue_eventqueue_id_seq'::regclass) NOT NULL,
event_time integer,
event_source smallint,
event_type smallint DEFAULT 0 NOT NULL,
event_meta text
); <-- Make sure you press Enter

You should see this output:

CREATE TABLE

Then do:

nagiosxi=# \q

Then repeat what ever caused the issue last time.

Adam Vandemore
Nagios Support Team

[avandemore]

Ok, I remember that. You had previous upgrade which failed due running out of disk space.

Please run this and provide the output:

Code: Select all

# rpm -qa |grep postgres

[valmberg]

# rpm -qa |grep postgres
postgresql-libs-8.4.20-1.el6_5.x86_64
postgresql-8.4.20-1.el6_5.x86_64
postgresql-server-8.4.20-1.el6_5.x86_64
postgresql-devel-8.4.20-1.el6_5.x86_64
#

[avandemore]

Please post the output from:

Code: Select all

# psql nagiosxi nagiosxi -c '\dt[S+]'

[valmberg]

Code: Select all

psql nagiosxi nagiosxi -c '\dt[S+]'

                                 List of relations
   Schema   |          Name           | Type  |  Owner   |    Size    | Description
------------+-------------------------+-------+----------+------------+-------------
 pg_catalog | pg_aggregate            | table | postgres | 8192 bytes |
 pg_catalog | pg_am                   | table | postgres | 8192 bytes |
 pg_catalog | pg_amop                 | table | postgres | 24 kB      |
 pg_catalog | pg_amproc               | table | postgres | 16 kB      |
 pg_catalog | pg_attrdef              | table | postgres | 16 kB      |
 pg_catalog | pg_attribute            | table | postgres | 368 kB     |
 pg_catalog | pg_auth_members         | table | postgres | 0 bytes    |
 pg_catalog | pg_authid               | table | postgres | 8192 bytes |
 pg_catalog | pg_cast                 | table | postgres | 16 kB      |
 pg_catalog | pg_class                | table | postgres | 64 kB      |
 pg_catalog | pg_constraint           | table | postgres | 8192 bytes |
 pg_catalog | pg_conversion           | table | postgres | 24 kB      |
 pg_catalog | pg_database             | table | postgres | 8192 bytes |
 pg_catalog | pg_depend               | table | postgres | 336 kB     |
 pg_catalog | pg_description          | table | postgres | 168 kB     |
 pg_catalog | pg_enum                 | table | postgres | 0 bytes    |
 pg_catalog | pg_foreign_data_wrapper | table | postgres | 0 bytes    |
 pg_catalog | pg_foreign_server       | table | postgres | 0 bytes    |
 pg_catalog | pg_index                | table | postgres | 24 kB      |
 pg_catalog | pg_inherits             | table | postgres | 0 bytes    |
 pg_catalog | pg_language             | table | postgres | 8192 bytes |
 pg_catalog | pg_largeobject          | table | postgres | 0 bytes    |
 pg_catalog | pg_listener             | table | postgres | 0 bytes    |
 pg_catalog | pg_namespace            | table | postgres | 8192 bytes |
 pg_catalog | pg_opclass              | table | postgres | 16 kB      |
 pg_catalog | pg_operator             | table | postgres | 104 kB     |
 pg_catalog | pg_opfamily             | table | postgres | 8192 bytes |
 pg_catalog | pg_pltemplate           | table | postgres | 8192 bytes |
 pg_catalog | pg_proc                 | table | postgres | 432 kB     |
 pg_catalog | pg_rewrite              | table | postgres | 72 kB      |
 pg_catalog | pg_shdepend             | table | postgres | 8192 bytes |
 pg_catalog | pg_shdescription        | table | postgres | 8192 bytes |
 pg_catalog | pg_statistic            | table | postgres | 224 kB     |
 pg_catalog | pg_tablespace           | table | postgres | 8192 bytes |
 pg_catalog | pg_trigger              | table | postgres | 8192 bytes |
 pg_catalog | pg_ts_config            | table | postgres | 8192 bytes |
 pg_catalog | pg_ts_config_map        | table | postgres | 16 kB      |
 pg_catalog | pg_ts_dict              | table | postgres | 8192 bytes |
 pg_catalog | pg_ts_parser            | table | postgres | 8192 bytes |
 pg_catalog | pg_ts_template          | table | postgres | 8192 bytes |
 pg_catalog | pg_type                 | table | postgres | 64 kB      |
 pg_catalog | pg_user_mapping         | table | postgres | 0 bytes    |
 public     | xi_auditlog             | table | nagiosxi | 280 kB     |
 public     | xi_commands             | table | nagiosxi | 8192 bytes |
 public     | xi_eventqueue           | table | postgres | 0 bytes    |
 public     | xi_events               | table | nagiosxi | 0 bytes    |
 public     | xi_incidents            | table | nagiosxi | 0 bytes    |
 public     | xi_meta                 | table | nagiosxi | 2504 kB    |
 public     | xi_options              | table | nagiosxi | 16 kB      |
 public     | xi_sysstat              | table | nagiosxi | 32 kB      |
 public     | xi_usermeta             | table | nagiosxi | 120 kB     |
 public     | xi_users                | table | nagiosxi | 8192 bytes |
(52 rows)

[avandemore]

Please run the following and if all show GRANT try the upgrade again:

Code: Select all

# psql -d nagiosxi -U postgres -c 'GRANT SELECT ON TABLE xi_auditlog TO nagiosxi;'
# psql -d nagiosxi -U postgres -c 'GRANT SELECT ON TABLE xi_commands TO nagiosxi;'
# psql -d nagiosxi -U postgres -c 'GRANT SELECT ON TABLE xi_events TO nagiosxi;'
# psql -d nagiosxi -U postgres -c 'GRANT SELECT ON TABLE xi_incidents TO nagiosxi;'
# psql -d nagiosxi -U postgres -c 'GRANT SELECT ON TABLE xi_meta TO nagiosxi;'
# psql -d nagiosxi -U postgres -c 'GRANT SELECT ON TABLE xi_options TO nagiosxi;'
# psql -d nagiosxi -U postgres -c 'GRANT SELECT ON TABLE xi_sysstat TO nagiosxi;'
# psql -d nagiosxi -U postgres -c 'GRANT SELECT ON TABLE xi_usermeta TO nagiosxi;'
# psql -d nagiosxi -U postgres -c 'GRANT SELECT ON TABLE xi_users TO nagiosxi;'

[valmberg]

I ran the statements you provided, including an extra one for

psql -d nagiosxi -U postgres -c 'GRANT SELECT ON TABLE xi_eventqueue TO nagiosxi;'

Now it returns this message:

Backing up MySQL databases...
Backing up PostgresQL databases...
pg_dump: SQL command failed
pg_dump: Error message from server: ERROR: permission denied for relation xi_eventqueue_eventqueue_id_seq
pg_dump: The command was: SELECT sequence_name, start_value, last_value, increment_by, CASE WHEN increment_by > 0 AND max_value = 9223372036854775807 THEN NULL WHEN increment_by < 0 AND max_value = -1 THEN NULL ELSE max_value END AS max_value, CASE WHEN increment_by > 0 AND min_value = 1 THEN NULL WHEN increment_by < 0 AND min_value = -9223372036854775807 THEN NULL ELSE min_value END AS min_value, cache_value, is_cycled, is_called from xi_eventqueue_eventqueue_id_seq
Error backing up PostgresQL database 'nagiosxi' !

[avandemore]

For all the prior tables again, run this (ex <Table Name> = xi_eventqueue) :

Code: Select all

psql -d nagiosxi -U postgres -c 'GRANT ALL PRIVILEGES ON TABLE <Table Name> TO nagiosxi;'