source: rattail-fabric/rattail_fabric/postgresql.py @ 12a29f2

Last change on this file since 12a29f2 was 12a29f2, checked in by Lance Edgar <lance@…>, 2 years ago

Tweak how postgresql version is detected

pattern match didn't work for the 10.5 version

  • Property mode set to 100644
File size: 6.3 KB
Line 
1# -*- coding: utf-8; -*-
2################################################################################
3#
4#  Rattail -- Retail Software Framework
5#  Copyright © 2010-2018 Lance Edgar
6#
7#  This file is part of Rattail.
8#
9#  Rattail is free software: you can redistribute it and/or modify it under the
10#  terms of the GNU General Public License as published by the Free Software
11#  Foundation, either version 3 of the License, or (at your option) any later
12#  version.
13#
14#  Rattail is distributed in the hope that it will be useful, but WITHOUT ANY
15#  WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
16#  FOR A PARTICULAR PURPOSE.  See the GNU General Public License for more
17#  details.
18#
19#  You should have received a copy of the GNU General Public License along with
20#  Rattail.  If not, see <http://www.gnu.org/licenses/>.
21#
22################################################################################
23"""
24Fabric Library for PostgreSQL
25"""
26
27from __future__ import unicode_literals, absolute_import
28
29import os
30import re
31
32from fabric.api import sudo, run, get, hide, abort, put, local
33
34from rattail_fabric import apt
35
36
37def install():
38    """
39    Install the PostgreSQL database service
40    """
41    apt.install('postgresql')
42
43
44def get_version():
45    """
46    Fetch the version of PostgreSQL running on the target system
47    """
48    result = sudo('psql --version')
49    if result.succeeded:
50        match = re.match(r'^psql \(PostgreSQL\) (\d+\.\d+)(?:\.\d+)?', result)
51        if match:
52            return float(match.group(1))
53
54
55def sql(sql, database='', port=None):
56    """
57    Execute some SQL as the 'postgres' user.
58    """
59    cmd = 'sudo -u postgres psql {port} --tuples-only --no-align --command="{sql}" {database}'.format(
60        port='--port={}'.format(port) if port else '',
61        sql=sql, database=database)
62    return sudo(cmd, shell=False)
63
64
65def script(path, database='', port=None, user=None, password=None):
66    """
67    Execute a SQL script.  By default this will run as 'postgres' user, but can
68    use PGPASSWORD authentication if necessary.
69    """
70    port = '--port={}'.format(port) if port else ''
71    if user and password:
72        with hide('running'):
73            kw = dict(pw=password, user=user, port=port, path=path, db=database)
74            return sudo(" PGPASSWORD='{pw}' psql --host=localhost {port} --username='{user}' --file='{path}' {db}".format(**kw))
75
76    else: # run as postgres
77        kw = dict(port=port, path=path, db=database)
78        return sudo("sudo -u postgres psql {port} --file='{path}' {db}".format(**kw), shell=False)
79
80
81def user_exists(name, port=None):
82    """
83    Determine if a given PostgreSQL user exists.
84    """
85    user = sql("SELECT rolname FROM pg_roles WHERE rolname = '{0}'".format(name), port=port)
86    return bool(user)
87
88
89def create_user(name, password=None, port=None, checkfirst=True, createdb=False):
90    """
91    Create a PostgreSQL user account.
92    """
93    if not checkfirst or not user_exists(name, port=port):
94        sudo('sudo -u postgres createuser {port} {createdb} --no-createrole --no-superuser {name}'.format(
95            port='--port={}'.format(port) if port else '',
96            createdb='--{}createdb'.format('' if createdb else 'no-'),
97            name=name))
98        if password:
99            set_user_password(name, password, port=port)
100
101
102def set_user_password(name, password, port=None):
103    """
104    Set the password for a PostgreSQL user account
105    """
106    with hide('running'):
107        sql("ALTER USER \\\"{}\\\" PASSWORD '{}';".format(name, password), port=port)
108
109
110def db_exists(name, port=None):
111    """
112    Determine if a given PostgreSQL database exists.
113    """
114    db = sql("SELECT datname FROM pg_database WHERE datname = '{0}'".format(name), port=port)
115    return db == name
116
117
118def create_db(name, owner=None, port=None, checkfirst=True):
119    """
120    Create a PostgreSQL database.
121    """
122    if not checkfirst or not db_exists(name, port=port):
123        cmd = 'sudo -u postgres createdb {port} {owner} {name}'.format(
124            port='--port={}'.format(port) if port else '',
125            owner='--owner={}'.format(owner) if owner else '',
126            name=name)
127        sudo(cmd, shell=False)
128
129
130def create_schema(name, dbname, owner='rattail', port=None):
131    """
132    Create a schema within a PostgreSQL database.
133    """
134    sql_ = "create schema if not exists {} authorization {}".format(name, owner)
135    sql(sql_, database=dbname, port=port)
136
137
138def drop_db(name, checkfirst=True):
139    """
140    Drop a PostgreSQL database.
141    """
142    if not checkfirst or db_exists(name):
143        sudo('sudo -u postgres dropdb {0}'.format(name), shell=False)
144
145
146def download_db(name, destination=None, port=None, exclude_tables=None):
147    """
148    Download a database from the "current" server.
149    """
150    if destination is None:
151        destination = './{0}.sql.gz'.format(name)
152    run('touch {0}.sql'.format(name))
153    run('chmod 0666 {0}.sql'.format(name))
154    sudo('sudo -u postgres pg_dump {port} {exclude_tables} --file={name}.sql {name}'.format(
155        name=name,
156        port='--port={}'.format(port) if port else '',
157        exclude_tables='--exclude-table-data={}'.format(exclude_tables) if exclude_tables else '',
158    ), shell=False)
159    run('gzip --force {0}.sql'.format(name))
160    get('{0}.sql.gz'.format(name), destination)
161    run('rm {0}.sql.gz'.format(name))
162
163
164def clone_db(name, owner, download, user='rattail', force=False, workdir=None):
165    """
166    Clone a database from a (presumably live) server
167
168    :param name: Name of the database.
169
170    :param owner: Username of the user who is to own the database.
171
172    :param force: Whether the target database should be forcibly dropped, if it
173       exists already.
174    """
175    if db_exists(name):
176       if force:
177           drop_db(name, checkfirst=False)
178       else:
179           abort("Database '{}' already exists!".format(name))
180
181    create_db(name, owner=owner, checkfirst=False)
182
183    # upload database dump to target server
184    if workdir:
185        curdir = os.getcwd()
186        os.chdir(workdir)
187    download('{}.sql.gz'.format(name), user=user)
188    put('{}.sql.gz'.format(name))
189    local('rm {}.sql.gz'.format(name))
190    if workdir:
191        os.chdir(curdir)
192
193    # restore database on target server
194    run('gunzip --force {}.sql.gz'.format(name))
195    sudo('sudo -u postgres psql --echo-errors --file={0}.sql {0}'.format(name), shell=False)
196    run('rm {}.sql'.format(name))
Note: See TracBrowser for help on using the repository browser.