click-odoo-backupdb: dump does not exlude non public data

[blaggacao]

https://stackoverflow.com/questions/10169203/postgresql-9-1-pg-restore-error-regarding-plpgsql

debugging output:

 /usr/bin/pg_restore --dbname=click-odoo-contrb-testbackupdb --no-owner /tmp/pytest-of-blaggacao/pytest-273/tests_backupdb_folder_restore1/backup/db.dump
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 4684; 0 0 COMMENT EXTENSION plpgsql 
pg_restore: [archiver (db)] could not execute query: ERROR:  must be owner of extension plpgsql
    Command was: COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';



WARNING: errors ignored on restore: 1

[blaggacao]

This change fixes it:
cmd = ["pg_dump", --no-owner", dbname] to
cmd = ["pg_dump", "--schema=public", "--no-owner", dbname]

Do you have access to a database expert to enlighten under what exact circumstances this is not risk free (other than using custom schema names, which not even sure is supported or not by odoo)?

[blaggacao]

The offending lines in the dump are:

SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;

--
-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: 
--

CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;


--
-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner: 
--

COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';


--
-- Name: base_cache_signaling; Type: SEQUENCE; Schema: public; Owner: blaggacao
--

CREATE SEQUENCE public.base_cache_signaling
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE

Note the CREATE is not the one that throws.

I'm doing:

blaggacao=> CREATE DATABASE test ENCODING "unicode" LC_COLLATE "C" TEMPLATE "template0";
CREATE DATABASE
blaggacao=> \c test
psql (11.1 (Ubuntu 11.1-1.pgdg18.04+1), server 10.6 (Ubuntu 10.6-1.pgdg18.04+1))
You are now connected to database "test" as user "blaggacao".
test=> CREATE EXTENSION plpgsql WITH SCHEMA pg_catalog;
ERROR:  extension "plpgsql" already exists
test=> COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
ERROR:  must be owner of extension plpgsql
test=>

which shows that the extension pre-exists on the database. Yet commenting is forbidden.

[blaggacao]

Yea, make the database user Superuser, I know. But I don't intent to do that.

[sbidoul]

Can we close this? I don't see any bug nor feature request so far.

[blaggacao]

Feel free to adapt this. I just wanted to feed back my findings over here.

[sbidoul]

My only question is: does click-odoo-backupdb produce the same output as the regular Odoo backup:

• If not I consider it a bug.
• If yes I'd say we need a very solid argument to change it.

@lmignon

[lmignon]

@sbidoul we produce the same output as the regular Odoo backup https://github.com/odoo/odoo/blob/12.0/odoo/service/db.py#L212
I don't see why we should hard-code a specific schema name. This could make the script unusable for other people.

[sbidoul]

Ok I close then. @blaggacao reopen with a proper bug description if you believe there is a bug (or better, open one at Odoo if it's related to the Odoo backup format).

[blaggacao]

@sbidoul you're right it's probably a "inconvenience" with the way odoo does it. Odoo, to my knowledge, does not ever use another than the public schema. So I understand it being an implicit design decision, however, Odoo does not comply with this purported decision in their backup implementation. Standard Odoo notoriety... 😉

[blaggacao]

odoo/odoo#31115

[lmignon]

@sbidoul @blaggacao I reopen this issue since it seems that the errors into travis for #38 are related to this issue.
I propose to add a new option to the command to allow the user to specify a list of schemas to dump (by default public)