PGMob - PostgreSQL Managed Objects

PGMob is a Python package that helps to simplify PostgreSQL administration by providing a layer of abstraction that allows you to write simple and easily understandable code instead of having to rely on SQL syntax. It’s your one tool that helps you to manage your PostgreSQL clusters on any scale and automate routine operations with ease.

PGMob abstracts away the complexity of SQL code and presents a user with a easy to use interface that controls most of the aspects of PostgreSQL administration. It will ensure you won’t have to switch between Python and SQL while building automation tasks and it will guide you through the process with type helpers and examples.

With PGMob, you can:

• Control your server while having access to only PostgreSQL protocol

• Ensure users, databases, and database objects define as you want them

• Execute backup/restore operations on your server without having to remember the command syntax

• Script and export your database objects on the fly

Example code

from pgmob import Cluster

cluster = Cluster(host="127.0.0.1", user="postgres", password="s3cur3p@ss")

# Execute a simple query with parameters
cluster.execute("SELECT tableowner FROM pg_tables WHERE tablename LIKE %s", "pg*")

# Create a new database owner and reassign ownership
owner_role = cluster.roles.new(name="db1owner", password="foobar")
owner_role.create()
db = cluster.databases["db1"]
db.owner = owner_role.name
db.alter()

# Modify pg_hba on the fly:
entry = "host all all 127.0.0.1/32 trust"
if entry not in cluster.hba_rules:
    cluster.hba_rules.extend(entry)
    cluster.hba_rules.alter()

# clone an existing role
sql = cluster.roles["somerole"].script()
cluster.execute(sql.replace("somerole", "newrole"))

# control access to your database
cluster.terminate(databases=["somedb"], roles=["someapp"])
cluster.databases["someotherdb"].disable()

# run backups/restores
from pgmob.backup import FileBackup, FileRestore

file_backup = FileBackup(cluster=cluster)
file_backup.options.schema_only = True
file_backup.backup(database="db1", path="/tmp/db.bak")

cluster.databases.new("db2").create()
file_restore = FileRestore(cluster=cluster)
file_restore.restore(database="db2", path="/tmp/db.bak")

# create, modify, and drop objects
cluster.schemas.new("app_schema").create()
for t in [t for t in cluster.tables if t.schema == "old_schema"]:
    t.schema = "app_schema"
    t.alter()
cluster.schemas["old_schema"].drop()

Installing

PGMob requires an adapter to talk to PostgreSQL, which it can detect automatically. Currently supported adapters:

• psycopg2

To install the module without an adapter (you would have to download it by other means) use

$ pip install -U pgmob

To include the adapter, use pip extras feature:

$ pip install -U pgmob[psycopg2]

Documentation

• Cluster object
  • Cluster
• Backup and Restore
  • Backup/restore options
  • File backup/restore
  • GCP Bucket backup/restore
• Managed Objects
  • Databases
  • Roles
  • Replication Slots
  • Tables
  • Views
  • Sequences
  • Procedures and Functions
  • Large Objects
  • Schemas
  • HBA Rule collection
• SQL statements API
  • Composable
  • Composed
  • Identifier
  • Literal
  • SQL
• Utilities API
  • OS Utilities
  • General Utilities
• Errors
  • General errors
  • Adapter errors
• Adapters API
  • ADAPTERS
  • detect_adapter()
  • Adapter abstract classes
  • psycopg2 adapter

Cluster object

Cluster object is your starting point of using PGMob. It establishes and maintains a connection to a PostgreSQL server and contains collections of asynchronous objects that help you build the blocks of automation using unified approach. Provide the connection information in the class constructor. The class constructor will accept any and all connection parameters supported by the underlying connection adapter, such as psycopg2.

from pgmob import Cluster

# connecting to a cluster as postgres
cluster = Cluster(host='localhost', user='postgres')
cluster.execute('SELECT a from table')

# connecting to a specific database
cluster = Cluster(user='mydbadmin', password='sup3rsec@re', dbname='mydb')
cluster.execute('SELECT a from table')

Managed Objects

Most of the Python objects in PGMob are asynchronously connected to the corresponding object on a remote PostgreSQL server. By changing their state via setting attribute values, you prepare and, eventually, push the changes to the remote. Such objects are called “Managed Objects”.

It is assumed that the provided connection credentials have all the required permissions to modify the objects on the remote server. Generally, we recommend to use SUPERUSER privileges when working with the module.

cluster = Cluster(user='postgres')

Managed objects can only be retrieved from the current connection context. To access objects from a different database, you need to connect to that database by adding a database argument to the Cluster object initialization.

The majority of Managed object collections are lazy-evaluated: they will be retrieved upon accessing them, and, subsequently, cached. To refresh the list of cached objects issue a .refresh() method of the collection:

cluster.tables.refresh()  # refresh table objects
# or
cluster.refresh()  # refresh all managed objects