3 # Script: pg_upgrade_conf.pl
4 # Copyright: 28/12/2014: Glyn Astill <glyn@8kb.co.uk>
5 # Requires: Perl 5.10.1+
7 # This script is a command-line utility to transplant PostgreSQL
8 # server settings from one conf file or server to another.
9 # Primarily intended for copying current settings in postgresql.conf
10 # into the default copy provided by a newer version to maintain
11 # information regarding new settings and defaults.
13 # This script is free software: you can redistribute it and/or modify
14 # it under the terms of the GNU General Public License as published by
15 # the Free Software Foundation, either version 3 of the License, or
16 # (at your option) any later version.
18 # This script is distributed in the hope that it will be useful,
19 # but WITHOUT ANY WARRANTY; without even the implied warranty of
20 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
21 # GNU General Public License for more details.
23 # You should have received a copy of the GNU General Public License
24 # along with this script. If not, see <http://www.gnu.org/licenses/>.
31 use Getopt::Long qw/GetOptions/;
32 Getopt::Long::Configure qw/no_ignore_case/;
34 use constant false => 0;
35 use constant true => 1;
40 my $g_page_size = 8192;
41 my $g_wal_segment_size = 2048;
42 my $g_wal_segment = sizePretty($g_page_size*$g_wal_segment_size);
44 # Settings that map onto obsolete settings
45 # <new_setting> <old_setting> <pretty modifier> <pretty units> <internal modifier>
46 my %g_gucs_map = (max_wal_size => [ split(' ', "checkpoint_segments *$g_wal_segment *1") ]);
48 # Obsolete settings , last version present and successor
49 my %g_gucs_obs = (max_fsm_pages => [ qw(8.3) ],
50 max_fsm_relations => [ qw(8.3) ],
51 ssl_renegotiation_limit => [ qw(9.3) ],
52 checkpoint_segments => [ qw(9.5 max_wal_size) ]);
54 my $g_non_default_count = 0;
55 my $g_change_count = 0;
56 my $g_usage = 'pg_upgrade_conf.pl { -f <path> [ -a <path> ] | -c <conninfo> } { -F <path> | -C <conninfo> }
58 -f --old_file Path to the old configuration file to read settings from
59 -a --old_auto_file Path to the old auto configuration file to read settings set via ALTER SYSTEM from
60 -c --old_conninfo Conninfo of the old server to read settings from
61 -F --new_file Path to the new configuration file to alter
62 -C --new_conninfo Conninfo of the new server to apply settings via ALTER SYSTEM';
66 die $g_usage unless GetOptions(\%opt, 'old_file|f=s', 'old_auto_file|a=s', 'new_file|F=s', 'old_conninfo|c=s', 'new_conninfo|C=s')
67 and keys %opt and ! @ARGV;
69 if (defined($opt{old_file})) {
70 loadSourceFromFile($opt{old_file});
71 if (defined($opt{old_auto_file})) {
72 loadSourceFromFile($opt{old_auto_file});
75 elsif (defined($opt{old_conninfo})) {
76 loadSourceFromConninfo($opt{old_conninfo});
79 print "Found $g_non_default_count non-default settings in old configuration\n";
81 if (defined($opt{new_file})) {
82 $g_change_count = modifyNewFile($opt{new_file});
84 elsif (defined($opt{new_conninfo})) {
85 $g_change_count = modifyNewConninfo($opt{new_conninfo});
86 if ($g_change_count > 0) {
87 print "WARNING: $g_change_count changes made to postgresql.auto.conf using ALTER SYSTEM; these will override any values set in postgresql.conf\n";
91 print "Made $g_change_count changes to new configuration\n";
93 sub loadSourceFromFile {
100 if (open(OLDFILE, "<", $old_file)) {
101 foreach (<OLDFILE>) {
103 unless ($_ =~ /^#/) {
106 s/#(?=(?:(?:[^']|[^"]*+'){2})*+[^']|[^"]*+\z).*//;
108 if (length(trim($_))) {
109 @fields = split('=', $_, 2);
110 $key = trim($fields[0]);
111 $value = trim($fields[1]);
114 print "DEBUG: Source file key = $key value = $value src = $src\n";
116 if (exists($g_gucs{$key})) {
117 print "WARNING: Source value for $key specified more than once, overwriting $g_gucs{$key} from $g_gucs_src{$key} with $value from $src\n";
120 $g_non_default_count++;
122 $g_gucs{$key} = $value;
123 $g_gucs_src{$key} = $src;
130 print "ERROR: Unable to open $old_file for reading\n";
134 sub loadSourceFromConninfo {
135 my $old_conninfo = shift;
147 $dsn = "DBI:Pg:$old_conninfo;";
149 $dbh = DBI->connect($dsn, '', '', {RaiseError => 1});
150 $query = "SELECT name, CASE vartype WHEN 'string' THEN quote_literal(reset_val) ELSE reset_val END, sourcefile
151 FROM pg_catalog.pg_settings WHERE boot_val <> reset_val AND source = 'configuration file'
152 AND context <> 'internal'";
153 $sth = $dbh->prepare($query);
155 while (my @fields = $sth->fetchrow) {
156 $key = trim($fields[0]);
157 $value = trim($fields[1]);
158 $src = trim($fields[2]);
160 print "DEBUG: Source setting key = $key value = $value src = $src\n";
162 $g_gucs{$key} = $value;
163 $g_gucs_src{$key} = $src;
164 $g_non_default_count++;
174 my $new_file = shift;
182 my $change_count = 0;
183 my $not_written = false;
185 if (open(NEWFILE, "<", $new_file)) {
186 foreach (<NEWFILE>) {
191 @fields = split('=', $_, 2);
193 $key =~ s/^#(.*)$/$1/;
195 $value = trim($fields[1]);
198 s/#(?=(?:(?:[^']|[^"]*+'){2})*+[^']|[^"]*+\z).*//;
200 $value = trim($value);
202 print "DEBUG: Target file key = $key value = $value\n";
204 if (!exists($g_gucs{$key}) && exists($g_gucs_map{$key}) && exists($g_gucs{$g_gucs_map{$key}[0]})) {
205 $g_gucs{$key} = eval($g_gucs{$g_gucs_map{$key}[0]} . $g_gucs_map{$key}[1]) . (defined($g_gucs_map{$key}[2])?"$g_gucs_map{$key}[2]":"");
206 $g_gucs_src{$key} .= "(mapped from $g_gucs_map{$key}[0] = $g_gucs{$g_gucs_map{$key}[0]})";
207 push(@lines, "# $g_gucs_map{$key}[0] = $g_gucs{$g_gucs_map{$key}[0]} # Obsoleted by $key as of pg $g_gucs_obs{$g_gucs_map{$key}[0]}[0] by $key = $g_gucs_map{$key}[0]$g_gucs_map{$key}[1] $g_gucs_map{$key}[2]");
208 $g_gucs{$g_gucs_map{$key}[0]} = '[written]';
210 if (exists($g_gucs{$key})) {
211 if (($_ !~ /^#/) && ($value eq $g_gucs{$key})) {
213 # Setting is already present and not commented out/default
214 print "$setting) Not setting $key : the values are already the same $value = $g_gucs{$key}\n";
215 $g_gucs{$key} = '[written]';
224 if ($g_gucs{$key} ne '[written]'){
226 print "$setting) Setting $key to $g_gucs{$key} : was " . (($comment)?"commented out / set to default":"set to") . " $value\n";
227 push(@lines, $key . ' = ' . $g_gucs{$key});
228 $g_gucs{$key} = '[written]';
240 foreach $key (keys %g_gucs) {
241 if ($g_gucs{$key} ne '[written]') {
249 push(@lines, '#' . '-'x78);
250 push(@lines, '# Unmatched settings written by pg_upgrade_conf.pl on ' . currentTimestamp());
251 push(@lines, '#' . '-'x78);
252 foreach $key (keys %g_gucs) {
253 if ($g_gucs{$key} ne '[written]') {
255 if (exists($g_gucs_obs{$key})) {
256 print "$setting) No place holder for setting $key : obsolete as of pg $g_gucs_obs{$key}[0] adding commented setting # $key = $g_gucs{$key}\n";
257 push(@lines, "# $key = $g_gucs{$key} # Obsolete as of pg $g_gucs_obs{$key}[0]");
260 print "$setting) No place holder for setting $key : adding setting $key = $g_gucs{$key}\n";
261 push(@lines, "$key = $g_gucs{$key}");
267 copy($new_file, $new_file . '.bak');
269 if (open(NEWFILE, ">", $new_file)) {
272 print "DEBUG: Line = $_\n";
274 print NEWFILE "$_\n";
279 print "ERROR: Unable to open $new_file for writing\n";
283 print "ERROR: Unable to open $new_file for reading\n";
285 return $change_count;
288 sub modifyNewConninfo {
289 my $new_conninfo = shift;
295 my $change_count = 0;
301 $dsn = "DBI:Pg:$new_conninfo;";
303 $dbh = DBI->connect($dsn, '', '', {RaiseError => 1});
304 $query = "SHOW server_version";
305 $sth = $dbh->prepare($query);
307 $version = $sth->fetchrow;
309 unless (substr($version,0,3) >= 9.4) {
310 die "PostgreSQL server version 9.4 or later required to apply changes with ALTER SYSTEM\n";
313 foreach $key (keys %g_gucs) {
320 if (exists($g_gucs_obs{$key}) && (substr($version,0,3) > $g_gucs_obs{$key}[0])) {
321 if (defined($g_gucs_obs{$key}[1])) {
322 $set_key = $g_gucs_obs{$key}[1];
323 $set_val = eval($g_gucs{$g_gucs_map{$set_key}[0]} . $g_gucs_map{$set_key}[3]);
324 $msg1 = "$g_gucs_obs{$key}[1] (replaces $key after pg $g_gucs_obs{$key}[0])";
325 $msg2 = "$set_val (instead of $key = $g_gucs{$key})\n";
328 print "$setting) Not setting $key : Setting obsolete after pg $g_gucs_obs{$key}[0] : would have been $key = $g_gucs{$key}\n";
334 $set_val = $g_gucs{$key};
336 $msg2 = $g_gucs{$key};
339 $query = "SELECT CASE vartype WHEN 'string' THEN quote_literal(reset_val) ELSE reset_val END
340 FROM pg_catalog.pg_settings WHERE lower(name) = lower(?)";
341 $sth = $dbh->prepare($query);
342 $sth->bind_param(1, $set_key);
344 $value = $sth->fetchrow;
346 print "$setting) KEY: $set_key SVAL: $value VAL: $set_val\n";
348 if ($value eq $set_val) {
349 print "$setting) Not setting $msg1 : the values are already the same $value = $set_val\n";
352 print "$setting) Setting $msg1 to $set_val : was set to $value\n";
353 $query = "ALTER SYSTEM SET " . qtrim($set_key) . " TO ?";
354 $sth = $dbh->prepare($query);
355 $sth->bind_param(1, qtrim($set_val));
366 return $change_count;
378 $string =~ s/^('|")+//;
379 $string =~ s/('|")+$//;
383 sub currentTimestamp{
385 my ($year, $month, $day, $hour, $min, $sec);
386 ($year, $month, $day, $hour, $min, $sec) = (localtime(time))[5,4,3,2,1,0];
387 $timestamp = sprintf ("%02d/%02d/%04d %02d:%02d:%02d", $day, $month+1, $year+1900, $hour, $min, $sec);
393 foreach ('B','KB','MB','GB','TB','PB')
395 return sprintf("%.2f",$size)." $_" if $size < 1024;