Skip to content

Commit c727513

Browse files
dmpgprodmpgpro
committed
Add pgpro-upgrade tool (based on PGPRO9_5)
1 parent b11e9bb commit c727513

File tree

6 files changed

+167
-1
lines changed

6 files changed

+167
-1
lines changed

‎src/Makefile

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -26,7 +26,8 @@ SUBDIRS = \
2626
bin \
2727
pl \
2828
makefiles \
29-
test/regress
29+
test/regress \
30+
pgpro-upgrade
3031

3132
# There are too many interdependencies between the subdirectories, so
3233
# don't attempt parallel make here.

‎src/pgpro-upgrade/001-pgpro_version.sql

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1 @@
1+
CREATE FUNCTION pg_catalog.pgpro_version() RETURNS TEXT AS 'pgpro_version' LANGUAGE internal STRICT IMMUTABLE;

‎src/pgpro-upgrade/001-pgpro_version.test

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1 @@
1+
SELECT COUNT(*) > 0 AS pgpro_version FROM pg_proc WHERE proname = 'pgpro_version' AND pronamespace = 11;

‎src/pgpro-upgrade/Makefile

Lines changed: 24 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,24 @@
1+
#-------------------------------------------------------------------------
2+
#
3+
# Makefile for src/pgpro-upgrade (Postgres Pro specific upgrade scripts)
4+
#
5+
# Copyright (c) 2016, Postgres Professional
6+
#
7+
# src/pgpro-upgrade/Makefile
8+
#
9+
#-------------------------------------------------------------------------
10+
11+
subdir = src/pgpro-upgrade
12+
top_builddir = ../..
13+
include $(top_builddir)/src/Makefile.global
14+
srcdir=$(top_srcdir)/$(subdir)
15+
16+
all:
17+
true
18+
19+
install: installdirs
20+
$(INSTALL_PROGRAM) $(srcdir)/pgpro_upgrade '$(DESTDIR)$(bindir)/pgpro_upgrade'
21+
$(INSTALL_DATA) $(srcdir)/*.sql $(srcdir)/*.test '$(DESTDIR)$(datadir)/pgpro-upgrade'
22+
installdirs:
23+
$(MKDIR_P) '$(DESTDIR)$(bindir)'
24+
$(MKDIR_P) '$(DESTDIR)$(datadir)/pgpro-upgrade'

‎src/pgpro-upgrade/README

Lines changed: 63 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,63 @@
1+
PGPRO upgarde system
2+
====================
3+
4+
This directory contains pgpro_upgrade script which allows to add some
5+
features (such as internal functions) into catalog of existing database.
6+
7+
HOW TO INVOKE SCRIPT
8+
--------------------
9+
10+
Script is intended to be run from postinst script of installable
11+
packages. It should be invoked as database owning system user
12+
(typically, 'postgres').
13+
14+
Script expects that PGDATA environment variable points to database
15+
cluster to be upgraded.
16+
17+
Script is installed into postgresql bin directory, and user can run it
18+
manually, if he has database cluster, which are not known by package
19+
configuration file.
20+
21+
Script should be invoked when instance of PostgreSQL which is upgraded,
22+
is stopped.
23+
24+
Script doesn't depend of any authentication settings in pg_hba.conf,
25+
because it starts postgres in single-user mode to perform upgrades.
26+
27+
It is safe to invoke script multiple times, because it does check for
28+
features it is going to add.
29+
30+
If script is invoked with **--check** argument, it doesn't do any
31+
changes in the database, just returns 0 if no changes is needed,
32+
and 1 if base needs to be upgraded.
33+
34+
HOW TO ADD NEW FEATURE
35+
----------------------
36+
37+
You need to add two sql files in this directory, if you want to add a
38+
feature.
39+
40+
Both files should have same base name and one — extension .test and
41+
other .sql
42+
43+
1. Script with .sql extension contains usual create commands which would
44+
create neccessary database object(s).
45+
46+
2. Script with .text extension should contain single select query,
47+
which returns single boolean value (i.e. one column and one row). This
48+
value should be 'f' if feature is not found in the database and should
49+
be installed, and 't' if it already exists.
50+
51+
Note that scirpts are invoked using postgres single-user mode, not using
52+
psql. So, each SQL statement should be in one line or line endings
53+
should be \ esaped.
54+
55+
Last line of the script should contain newline at the end.
56+
57+
Script naming convention
58+
------------------------
59+
60+
Scripts are executed in the lexicographical order of their names.
61+
So, please start name of script with 3-digit number, next one to last
62+
used, followed by dash.
63+

‎src/pgpro-upgrade/pgpro_upgrade

Lines changed: 76 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,76 @@
1+
#!/bin/sh
2+
# Utilities needed
3+
# 1. sh
4+
# 3. sed
5+
if [ -z "$PGDATA" ]; then
6+
echo "PGDATA environment variable is not set. Stop." 1>&2
7+
exit 2
8+
fi
9+
if [ -e "$PGDATA/recovery.conf" ]; then
10+
echo "recovery.conf exists in PGDATA. No upgrade script could be run." 1>&2
11+
exit 0
12+
fi
13+
PGBIN="`echo $0|sed 's![^/\\]*$!!'`"
14+
if [ "$1" = "--check" ]; then
15+
check=1
16+
else
17+
check=
18+
fi
19+
echo "$PGBIN"
20+
21+
case "$PGBIN" in
22+
*/bin/|*\\bin\\)
23+
PGSHARE="`echo $PGBIN|sed 's!bin.$!share!'`"
24+
;;
25+
*) PGBIN=""
26+
PGSHARE=""
27+
;;
28+
esac
29+
30+
for dir in "$PGSHARE" /usr/pgsql-9.5/share /usr/share/postgresql/9.5 /usr/pgsql/9.5/share /usr/share/pgsql /usr/share/postgrespro95 ; do
31+
if [ -d "$dir/pgpro-upgrade" ]; then
32+
DIR="$dir/pgpro-upgrade"
33+
break
34+
fi
35+
done
36+
if [ -z "$DIR" ]; then
37+
echo "Cannot find feature scripts" 1>&2
38+
exit 1
39+
fi
40+
BASELIST=`echo "select datname from pg_database;"|
41+
"${PGBIN}postgres" --single template0 |
42+
sed -n 's/^.*1: datname = "\([^"]*\)".*$/\1/p'`
43+
44+
if [ -z "$BASELIST" ]; then
45+
echo "Databases for upgrade not found" 1>&2
46+
exit 1
47+
fi
48+
49+
[ -z "$check" ]&& echo "Upgrading databases $BASELIST"
50+
51+
#Search for upgrade scripts
52+
need_upgrade=0
53+
for i in "$DIR"/*.test; do
54+
found=`< "$i" "${PGBIN}postgres" --single template0 |
55+
sed -n 's/^[ ]*1: [^ ]* = "\([ft]\)"[ ].*$/\1/p'`
56+
if [ "$found" = "f" ]; then
57+
if [ -z "$check" ]; then
58+
create="`echo "$i" |sed 's/\.test$/.sql/'`"
59+
for base in $BASELIST; do
60+
echo "Executing $create in $base"
61+
< "$create" "${PGBIN}postgres" --single "$base" >/dev/null
62+
63+
done
64+
else
65+
need_upgrade=1
66+
fi
67+
fi
68+
done
69+
if [ -n "$check" ]; then
70+
if [ $need_upgrade -eq 0 ]; then
71+
echo "All Postgres Pro specific updates are appied" 1>&2
72+
else
73+
echo "Database needs upgrade"
74+
fi
75+
exit $need_upgrade
76+
fi

0 commit comments

Comments
 (0)