Ejemplo n.º 1
0
    def __get_general_pub_info(self):
        """Get and return general publication information.

        Returns:
            Dict with publication information if successful, False otherwise.
        """
        # Check pg_publication.pubtruncate exists (supported from PostgreSQL 11):
        pgtrunc_sup = exec_sql(self,
                               ("SELECT 1 FROM information_schema.columns "
                                "WHERE table_name = 'pg_publication' "
                                "AND column_name = 'pubtruncate'"),
                               add_to_executed=False)

        if pgtrunc_sup:
            query = (
                "SELECT r.rolname AS pubowner, p.puballtables, p.pubinsert, "
                "p.pubupdate , p.pubdelete, p.pubtruncate FROM pg_publication AS p "
                "JOIN pg_catalog.pg_roles AS r "
                "ON p.pubowner = r.oid "
                "WHERE p.pubname = '%s'" % self.name)
        else:
            query = (
                "SELECT r.rolname AS pubowner, p.puballtables, p.pubinsert, "
                "p.pubupdate , p.pubdelete FROM pg_publication AS p "
                "JOIN pg_catalog.pg_roles AS r "
                "ON p.pubowner = r.oid "
                "WHERE p.pubname = '%s'" % self.name)

        result = exec_sql(self, query, add_to_executed=False)
        if result:
            return result[0]
        else:
            return False
    def __fill_out_info(self,
                        result,
                        info_key=None,
                        schema_key=None,
                        name_key=None):
        # Convert result to list of dicts to handle it easier:
        result = [dict(row) for row in result]

        for elem in result:
            # Add schema name as a key if not presented:
            if not self.info[info_key].get(elem[schema_key]):
                self.info[info_key][elem[schema_key]] = {}

            # Add object name key as a subkey
            # (they must be uniq over a schema, so no need additional checks):
            self.info[info_key][elem[schema_key]][elem[name_key]] = {}

            # Add other other attributes to a certain index:
            for key, val in iteritems(elem):
                if key not in (schema_key, name_key):
                    self.info[info_key][elem[schema_key]][
                        elem[name_key]][key] = val

            if info_key in ('tables', 'indexes'):
                relname = elem[name_key]
                schemaname = elem[schema_key]
                if not self.schema:
                    result = exec_sql(self,
                                      "SELECT pg_relation_size ('%s.%s')" %
                                      (schemaname, relname),
                                      add_to_executed=False)
                else:
                    relname = '%s.%s' % (self.schema, relname)
                    result = exec_sql(self,
                                      "SELECT pg_relation_size (%s)",
                                      query_params=(relname, ),
                                      add_to_executed=False)

                self.info[info_key][elem[schema_key]][
                    elem[name_key]]['size'] = result[0][0]

                if info_key == 'tables':
                    relname = elem[name_key]
                    schemaname = elem[schema_key]
                    if not self.schema:
                        result = exec_sql(
                            self,
                            "SELECT pg_total_relation_size ('%s.%s')" %
                            (schemaname, relname),
                            add_to_executed=False)
                    else:
                        relname = '%s.%s' % (self.schema, relname)
                        result = exec_sql(self,
                                          "SELECT pg_total_relation_size (%s)",
                                          query_params=(relname, ),
                                          add_to_executed=False)

                        self.info[info_key][elem[schema_key]][
                            elem[name_key]]['total_size'] = result[0][0]
Ejemplo n.º 3
0
    def get_info(self):
        """Get tablespace information."""
        # Check that spcoptions exists:
        opt = exec_sql(self, "SELECT 1 FROM information_schema.columns "
                       "WHERE table_name = 'pg_tablespace' "
                       "AND column_name = 'spcoptions'",
                       add_to_executed=False)

        # For 9.1 version and earlier:
        location = exec_sql(self, "SELECT 1 FROM information_schema.columns "
                            "WHERE table_name = 'pg_tablespace' "
                            "AND column_name = 'spclocation'",
                            add_to_executed=False)
        if location:
            location = 'spclocation'
        else:
            location = 'pg_tablespace_location(t.oid)'

        if not opt:
            self.opt_not_supported = True
            query = ("SELECT r.rolname, (SELECT Null), %s "
                     "FROM pg_catalog.pg_tablespace AS t "
                     "JOIN pg_catalog.pg_roles AS r "
                     "ON t.spcowner = r.oid " % location)
        else:
            query = ("SELECT r.rolname, t.spcoptions, %s "
                     "FROM pg_catalog.pg_tablespace AS t "
                     "JOIN pg_catalog.pg_roles AS r "
                     "ON t.spcowner = r.oid " % location)

        res = exec_sql(self,
                       query + "WHERE t.spcname = %(name)s",
                       query_params={'name': self.name},
                       add_to_executed=False)

        if not res:
            self.exists = False
            return False

        if res[0][0]:
            self.exists = True
            self.owner = res[0][0]

            if res[0][1]:
                # Options exist:
                for i in res[0][1]:
                    i = i.split('=')
                    self.settings[i[0]] = i[1]

            if res[0][2]:
                # Location exists:
                self.location = res[0][2]
Ejemplo n.º 4
0
    def set_owner(self):
        """Implements ALTER SEQUENCE OWNER TO command behavior."""
        query = ['ALTER SEQUENCE']
        query.append(self.__add_schema())
        query.append('OWNER TO %s' % pg_quote_identifier(self.module.params['owner'], 'role'))

        return exec_sql(self, ' '.join(query), ddl=True)
Ejemplo n.º 5
0
    def copy_from(self):
        """Implements COPY FROM command behavior."""
        self.src = self.module.params['copy_from']
        self.dst = self.module.params['dst']

        query_fragments = ['COPY %s' % pg_quote_identifier(self.dst, 'table')]

        if self.module.params.get('columns'):
            query_fragments.append('(%s)' %
                                   ','.join(self.module.params['columns']))

        query_fragments.append('FROM')

        if self.module.params.get('program'):
            query_fragments.append('PROGRAM')

        query_fragments.append("'%s'" % self.src)

        if self.module.params.get('options'):
            query_fragments.append(self.__transform_options())

        # Note: check mode is implemented here:
        if self.module.check_mode:
            self.changed = self.__check_table(self.dst)

            if self.changed:
                self.executed_queries.append(' '.join(query_fragments))
        else:
            if exec_sql(self, ' '.join(query_fragments), ddl=True):
                self.changed = True
Ejemplo n.º 6
0
    def reassign(self, old_owners, fail_on_role):
        """Implements REASSIGN OWNED BY command.

        If success, set self.changed as True.

        Arguments:
            old_owners (list): The ownership of all the objects within
                the current database, and of all shared objects (databases, tablespaces),
                owned by these roles will be reassigned to self.role.
            fail_on_role (bool): If True, fail when a role from old_owners does not exist.
                Otherwise just warn and continue.
        """
        roles = []
        for r in old_owners:
            if self.check_role_exists(r, fail_on_role):
                roles.append(pg_quote_identifier(r, 'role'))

        # Roles do not exist, nothing to do, exit:
        if not roles:
            return False

        old_owners = ','.join(roles)

        query = ['REASSIGN OWNED BY']
        query.append(old_owners)
        query.append('TO %s' % pg_quote_identifier(self.role, 'role'))
        query = ' '.join(query)

        self.changed = exec_sql(self, query, ddl=True)
Ejemplo n.º 7
0
    def __get_general_subscr_info(self):
        """Get and return general subscription information.

        Returns:
            Dict with subscription information if successful, False otherwise.
        """
        query = ("SELECT d.datname, r.rolname, s.subenabled, "
                 "s.subconninfo, s.subslotname, s.subsynccommit, "
                 "s.subpublications FROM pg_catalog.pg_subscription s "
                 "JOIN pg_catalog.pg_database d "
                 "ON s.subdbid = d.oid "
                 "JOIN pg_catalog.pg_roles AS r "
                 "ON s.subowner = r.oid "
                 "WHERE s.subname = %(name)s AND d.datname = %(db)s")

        result = exec_sql(self,
                          query,
                          query_params={
                              'name': self.name,
                              'db': self.db
                          },
                          add_to_executed=False)
        if result:
            return result[0]
        else:
            return False
Ejemplo n.º 8
0
    def drop(self):
        """Drop tablespace.

        Return True if success, otherwise, return False.
        """

        return exec_sql(self, "DROP TABLESPACE %s" % pg_quote_identifier(self.name, 'database'), ddl=True)
Ejemplo n.º 9
0
    def __get_general_sub_info(self):
        """Get and return general subscription information.

        Returns:
            Dict with subscription information if successful, False otherwise.
        """

        query = (" SELECT "
                 "r.rolname AS subowner, "
                 "s.subdbid, "
                 "s.subname, "
                 "s.subenabled, "
                 "s.subsynccommit, "
                 "s.subconninfo, "
                 "s.subslotname, "
                 "s.subpublications "
                 "FROM pg_subscription AS s "
                 "JOIN pg_catalog.pg_roles AS r ON s.subowner = r.oid "
                 "WHERE s.subname = '%s'" % self.name)

        result = exec_sql(self, query, add_to_executed=False)
        pprint.pprint('temp log: result' + str(result))

        if result:
            return result[0]
        else:
            return False
Ejemplo n.º 10
0
    def create(self):
        """Implements CREATE SEQUENCE command behavior."""
        query = ['CREATE SEQUENCE']
        query.append(self.__add_schema())

        if self.module.params.get('data_type'):
            query.append('AS %s' % self.module.params['data_type'])

        if self.module.params.get('increment'):
            query.append('INCREMENT BY %s' % self.module.params['increment'])

        if self.module.params.get('minvalue'):
            query.append('MINVALUE %s' % self.module.params['minvalue'])

        if self.module.params.get('maxvalue'):
            query.append('MAXVALUE %s' % self.module.params['maxvalue'])

        if self.module.params.get('start'):
            query.append('START WITH %s' % self.module.params['start'])

        if self.module.params.get('cache'):
            query.append('CACHE %s' % self.module.params['cache'])

        if self.module.params.get('cycle'):
            query.append('CYCLE')

        return exec_sql(self, ' '.join(query), ddl=True)
Ejemplo n.º 11
0
    def __exists_in_db(self):
        """Check table exists and refresh info"""
        if "." in self.name:
            schema = self.name.split('.')[-2]
            tblname = self.name.split('.')[-1]
        else:
            schema = 'public'
            tblname = self.name

        query = ("SELECT t.tableowner, t.tablespace, c.reloptions "
                 "FROM pg_tables AS t "
                 "INNER JOIN pg_class AS c ON  c.relname = t.tablename "
                 "INNER JOIN pg_namespace AS n ON c.relnamespace = n.oid "
                 "WHERE t.tablename = %(tblname)s "
                 "AND n.nspname = %(schema)s")
        res = exec_sql(self, query, query_params={'tblname': tblname, 'schema': schema},
                       add_to_executed=False)
        if res:
            self.exists = True
            self.info = dict(
                owner=res[0][0],
                tblspace=res[0][1] if res[0][1] else '',
                storage_params=res[0][2] if res[0][2] else [],
            )

            return True
        else:
            self.exists = False
            return False
Ejemplo n.º 12
0
    def set_schema(self):
        """Implements ALTER SEQUENCE SET SCHEMA command behavior."""
        query = ['ALTER SEQUENCE']
        query.append(self.__add_schema())
        query.append('SET SCHEMA %s' % pg_quote_identifier(self.module.params['newschema'], 'schema'))

        return exec_sql(self, ' '.join(query), ddl=True)
Ejemplo n.º 13
0
    def drop(self, schema, cascade=False, concurrent=True):
        """Drop PostgreSQL index.

        Return True if success, otherwise, return False.

        Args:
            schema (str) -- name of the index schema

        Kwargs:
            cascade (bool) -- automatically drop objects that depend on the index,
                default False
            concurrent (bool) -- build index in concurrent mode, default True
        """
        if not self.exists:
            return False

        query = 'DROP INDEX'

        if concurrent:
            query += ' CONCURRENTLY'

        if not schema:
            query += ' public.%s' % self.name
        else:
            query += ' %s.%s' % (schema, self.name)

        if cascade:
            query += ' CASCADE'

        self.executed_query = query

        if exec_sql(self, query, ddl=True, add_to_executed=False):
            return True

        return False
Ejemplo n.º 14
0
    def create(self,
               kind='physical',
               immediately_reserve=False,
               output_plugin=False,
               just_check=False):
        if self.exists:
            if self.kind == kind:
                return False
            else:
                self.module.warn("slot with name '%s' already exists "
                                 "but has another type '%s'" %
                                 (self.name, self.kind))
                return False

        if just_check:
            return None

        if kind == 'physical':
            # Check server version (needs for immedately_reserverd needs 9.6+):
            if self.cursor.connection.server_version < 96000:
                query = "SELECT pg_create_physical_replication_slot('%s')" % self.name

            else:
                query = "SELECT pg_create_physical_replication_slot('%s', %s)" % (
                    self.name, immediately_reserve)

        elif kind == 'logical':
            query = "SELECT pg_create_logical_replication_slot('%s', '%s')" % (
                self.name, output_plugin)

        self.changed = exec_sql(self, query, ddl=True)
Ejemplo n.º 15
0
    def rename(self):
        """Implements ALTER SEQUENCE RENAME TO command behavior."""
        query = ['ALTER SEQUENCE']
        query.append(self.__add_schema())
        query.append('RENAME TO %s' % pg_quote_identifier(self.module.params['rename_to'], 'sequence'))

        return exec_sql(self, ' '.join(query), ddl=True)
Ejemplo n.º 16
0
    def __exists_in_db(self):
        """Check index existence, collect info, add it to self.info dict.

        Return True if the index exists, otherwise, return False.
        """
        query = ("SELECT i.schemaname, i.tablename, i.tablespace, "
                 "pi.indisvalid, c.reloptions "
                 "FROM pg_catalog.pg_indexes AS i "
                 "JOIN pg_catalog.pg_class AS c "
                 "ON i.indexname = c.relname "
                 "JOIN pg_catalog.pg_index AS pi "
                 "ON c.oid = pi.indexrelid "
                 "WHERE i.indexname = %(name)s")

        res = exec_sql(self,
                       query,
                       query_params={'name': self.name},
                       add_to_executed=False)
        if res:
            self.exists = True
            self.info = dict(
                name=self.name,
                state='present',
                schema=res[0][0],
                tblname=res[0][1],
                tblspace=res[0][2] if res[0][2] else '',
                valid=res[0][3],
                storage_params=res[0][4] if res[0][4] else [],
            )
            return True

        else:
            self.exists = False
            return False
Ejemplo n.º 17
0
    def copy_to(self):
        """Implements COPY TO command behavior."""
        self.src = self.module.params['src']
        self.dst = self.module.params['copy_to']

        if 'SELECT ' in self.src.upper():
            # If src is SQL SELECT statement:
            query_fragments = ['COPY (%s)' % self.src]
        else:
            # If src is a table:
            query_fragments = ['COPY %s' % pg_quote_identifier(self.src, 'table')]

        if self.module.params.get('columns'):
            query_fragments.append('(%s)' % ','.join(self.module.params['columns']))

        query_fragments.append('TO')

        if self.module.params.get('program'):
            query_fragments.append('PROGRAM')

        query_fragments.append("'%s'" % self.dst)

        if self.module.params.get('options'):
            query_fragments.append(self.__transform_options())

        # Note: check mode is implemented here:
        if self.module.check_mode:
            self.changed = self.__check_table(self.src)

            if self.changed:
                self.executed_queries.append(' '.join(query_fragments))
        else:
            if exec_sql(self, ' '.join(query_fragments), ddl=True):
                self.changed = True
Ejemplo n.º 18
0
    def create(self,
               tblname,
               idxtype,
               columns,
               cond,
               tblspace,
               storage_params,
               concurrent=True):
        """Create PostgreSQL index.

        Return True if success, otherwise, return False.

        Args:
            tblname (str) -- name of a table for the index
            idxtype (str) -- type of the index like BTREE, BRIN, etc
            columns (str) -- string of comma-separated columns that need to be covered by index
            tblspace (str) -- tablespace for storing the index
            storage_params (str) -- string of comma-separated storage parameters

        Kwargs:
            concurrent (bool) -- build index in concurrent mode, default True
        """

        if self.exists:
            return False

        changed = False
        if idxtype is None:
            idxtype = "BTREE"

        query = 'CREATE INDEX'

        if concurrent:
            query += ' CONCURRENTLY'

        query += ' %s' % self.name

        if self.schema:
            query += ' ON %s.%s ' % (self.schema, tblname)
        else:
            query += 'public.%s ' % tblname

        query += 'USING %s (%s)' % (idxtype, columns)

        if storage_params:
            query += ' WITH (%s)' % storage_params

        if tblspace:
            query += ' TABLESPACE %s' % tblspace

        if cond:
            query += ' WHERE %s' % cond

        self.executed_query = query

        if exec_sql(self, query, ddl=True, add_to_executed=False):
            return True

        return False
Ejemplo n.º 19
0
    def drop(self, cascade=False):
        if not self.exists:
            return False

        query = "DROP TABLE %s" % pg_quote_identifier(self.name, 'table')
        if cascade:
            query += " CASCADE"
        return exec_sql(self, query, ddl=True)
Ejemplo n.º 20
0
    def drop(self):
        if not self.exists:
            return False

        query = "SELECT pg_drop_replication_slot(%(name)s)"
        self.changed = exec_sql(self,
                                query,
                                query_params={'name': self.name},
                                ddl=True)
Ejemplo n.º 21
0
    def drop(self):
        """Implements DROP SEQUENCE command behavior."""
        query = ['DROP SEQUENCE']
        query.append(self.__add_schema())

        if self.module.params.get('cascade'):
            query.append('CASCADE')

        return exec_sql(self, ' '.join(query), ddl=True)
Ejemplo n.º 22
0
 def __slot_exists(self):
     query = "SELECT slot_type FROM pg_replication_slots WHERE slot_name = %(name)s"
     res = exec_sql(self,
                    query,
                    query_params={'name': self.name},
                    add_to_executed=False)
     if res:
         self.exists = True
         self.kind = res[0][0]
Ejemplo n.º 23
0
    def __get_tables_pub_info(self):
        """Get and return tables that are published by the publication.

        Returns:
            List of dicts with published tables.
        """
        query = ("SELECT schemaname, tablename "
                 "FROM pg_publication_tables WHERE pubname = '%s'" % self.name)
        return exec_sql(self, query, add_to_executed=False)
    def __set_setting(self, setting):
        """Set tablespace setting.

        Return True if success, otherwise, return False.

        args:
            setting (str) -- string in format "setting_name = 'setting_value'"
        """
        query = "ALTER TABLESPACE %s SET (%s)" % (pg_quote_identifier(self.name, 'database'), setting)
        return exec_sql(self, query, ddl=True)
    def create(self, location):
        """Create tablespace.

        Return True if success, otherwise, return False.

        args:
            location (str) -- tablespace directory path in the FS
        """
        query = ("CREATE TABLESPACE %s LOCATION '%s'" % (pg_quote_identifier(self.name, 'database'), location))
        return exec_sql(self, query, ddl=True)
Ejemplo n.º 26
0
 def get_pg_version(self):
     query = "SELECT version()"
     raw = exec_sql(self, query, add_to_executed=False)[0][0]
     if raw:
         self.is_available = True
         raw = raw.split()[1].split('.')
         self.version = dict(
             major=int(raw[0]),
             minor=int(raw[1]),
         )
    def get_tbl_stat(self):
        """Get table statistics and fill out self.info dictionary."""
        if not self.schema:
            query = "SELECT * FROM pg_stat_user_tables"
            result = exec_sql(self, query, add_to_executed=False)
        else:
            query = "SELECT * FROM pg_stat_user_tables WHERE schemaname = %s"
            result = exec_sql(self,
                              query,
                              query_params=(self.schema, ),
                              add_to_executed=False)

        if not result:
            return

        self.__fill_out_info(result,
                             info_key='tables',
                             schema_key='schemaname',
                             name_key='relname')
    def rename(self, newname):
        """Rename tablespace.

        Return True if success, otherwise, return False.

        args:
            newname (str) -- new name for the tablespace"
        """
        query = "ALTER TABLESPACE %s RENAME TO %s" % (pg_quote_identifier(self.name, 'database'), newname)
        self.new_name = newname
        return exec_sql(self, query, ddl=True)
Ejemplo n.º 29
0
    def __check_table(self, table):
        """Check table or SQL in transaction mode for check_mode.

        Return True if it is OK.

        Arguments:
            table (str) - Table name that needs to be checked.
                It can be SQL SELECT statement that was passed
                instead of the table name.
        """
        if 'SELECT ' in table.upper():
            # In this case table is actually SQL SELECT statement.
            # If SQL fails, it's handled by exec_sql():
            exec_sql(self, table, add_to_executed=False)
            # If exec_sql was passed, it means all is OK:
            return True

        exec_sql(self, 'SELECT 1 FROM %s' % pg_quote_identifier(table, 'table'),
                 add_to_executed=False)
        # If SQL was executed successfully:
        return True
Ejemplo n.º 30
0
    def create_like(self,
                    src_table,
                    including='',
                    tblspace='',
                    unlogged=False,
                    params='',
                    owner=''):
        """
        Create table like another table (with similar DDL).
        Arguments:
        src_table - source table.
        including - corresponds to optional INCLUDING expression
            in CREATE TABLE ... LIKE statement.
        params - storage params (passed by "WITH (...)" in SQL),
            comma separated.
        tblspace - tablespace.
        owner - table owner.
        unlogged - create unlogged table.
        """
        changed = False

        name = pg_quote_identifier(self.name, 'table')

        query = "CREATE"
        if unlogged:
            query += " UNLOGGED TABLE %s" % name
        else:
            query += " TABLE %s" % name

        query += " (LIKE %s" % pg_quote_identifier(src_table, 'table')

        if including:
            including = including.split(',')
            for i in including:
                query += " INCLUDING %s" % i

        query += ')'

        if params:
            query += " WITH (%s)" % params

        if tblspace:
            query += " TABLESPACE %s" % pg_quote_identifier(
                tblspace, 'database')

        if exec_sql(self, query, ddl=True):
            changed = True

        if owner:
            changed = self.set_owner(owner)

        return changed