PostgreSQL Source Code  git master
pg_backup_db.c
Go to the documentation of this file.
1 /*-------------------------------------------------------------------------
2  *
3  * pg_backup_db.c
4  *
5  * Implements the basic DB functions used by the archiver.
6  *
7  * IDENTIFICATION
8  * src/bin/pg_dump/pg_backup_db.c
9  *
10  *-------------------------------------------------------------------------
11  */
12 #include "postgres_fe.h"
13 
14 #include <unistd.h>
15 #include <ctype.h>
16 #ifdef HAVE_TERMIOS_H
17 #include <termios.h>
18 #endif
19 
20 #include "common/connect.h"
21 #include "common/string.h"
22 #include "dumputils.h"
23 #include "fe_utils/string_utils.h"
24 #include "parallel.h"
25 #include "pg_backup_archiver.h"
26 #include "pg_backup_db.h"
27 #include "pg_backup_utils.h"
28 
29 static void _check_database_version(ArchiveHandle *AH);
30 static void notice_processor(void *arg, const char *message);
31 
32 static void
34 {
35  const char *remoteversion_str;
36  int remoteversion;
37  PGresult *res;
38 
39  remoteversion_str = PQparameterStatus(AH->connection, "server_version");
40  remoteversion = PQserverVersion(AH->connection);
41  if (remoteversion == 0 || !remoteversion_str)
42  pg_fatal("could not get server_version from libpq");
43 
44  AH->public.remoteVersionStr = pg_strdup(remoteversion_str);
45  AH->public.remoteVersion = remoteversion;
46  if (!AH->archiveRemoteVersion)
48 
49  if (remoteversion != PG_VERSION_NUM
50  && (remoteversion < AH->public.minRemoteVersion ||
51  remoteversion > AH->public.maxRemoteVersion))
52  {
53  pg_log_error("aborting because of server version mismatch");
54  pg_log_error_detail("server version: %s; %s version: %s",
55  remoteversion_str, progname, PG_VERSION);
56  exit(1);
57  }
58 
59  /*
60  * Check if server is in recovery mode, which means we are on a hot
61  * standby.
62  */
64  "SELECT pg_catalog.pg_is_in_recovery()");
65  AH->public.isStandby = (strcmp(PQgetvalue(res, 0, 0), "t") == 0);
66  PQclear(res);
67 }
68 
69 /*
70  * Reconnect to the server. If dbname is not NULL, use that database,
71  * else the one associated with the archive handle.
72  */
73 void
75 {
76  PGconn *oldConn = AH->connection;
77  RestoreOptions *ropt = AH->public.ropt;
78 
79  /*
80  * Save the dbname, if given, in override_dbname so that it will also
81  * affect any later reconnection attempt.
82  */
83  if (dbname)
85 
86  /*
87  * Note: we want to establish the new connection, and in particular update
88  * ArchiveHandle's connCancel, before closing old connection. Otherwise
89  * an ill-timed SIGINT could try to access a dead connection.
90  */
91  AH->connection = NULL; /* dodge error check in ConnectDatabase */
92 
93  ConnectDatabase((Archive *) AH, &ropt->cparams, true);
94 
95  PQfinish(oldConn);
96 }
97 
98 /*
99  * Make, or remake, a database connection with the given parameters.
100  *
101  * The resulting connection handle is stored in AHX->connection.
102  *
103  * An interactive password prompt is automatically issued if required.
104  * We store the results of that in AHX->savedPassword.
105  * Note: it's not really all that sensible to use a single-entry password
106  * cache if the username keeps changing. In current usage, however, the
107  * username never does change, so one savedPassword is sufficient.
108  */
109 void
111  const ConnParams *cparams,
112  bool isReconnect)
113 {
114  ArchiveHandle *AH = (ArchiveHandle *) AHX;
115  trivalue prompt_password;
116  char *password;
117  bool new_pass;
118 
119  if (AH->connection)
120  pg_fatal("already connected to a database");
121 
122  /* Never prompt for a password during a reconnection */
123  prompt_password = isReconnect ? TRI_NO : cparams->promptPassword;
124 
125  password = AH->savedPassword;
126 
127  if (prompt_password == TRI_YES && password == NULL)
128  password = simple_prompt("Password: ", false);
129 
130  /*
131  * Start the connection. Loop until we have a password if requested by
132  * backend.
133  */
134  do
135  {
136  const char *keywords[8];
137  const char *values[8];
138  int i = 0;
139 
140  /*
141  * If dbname is a connstring, its entries can override the other
142  * values obtained from cparams; but in turn, override_dbname can
143  * override the dbname component of it.
144  */
145  keywords[i] = "host";
146  values[i++] = cparams->pghost;
147  keywords[i] = "port";
148  values[i++] = cparams->pgport;
149  keywords[i] = "user";
150  values[i++] = cparams->username;
151  keywords[i] = "password";
152  values[i++] = password;
153  keywords[i] = "dbname";
154  values[i++] = cparams->dbname;
155  if (cparams->override_dbname)
156  {
157  keywords[i] = "dbname";
158  values[i++] = cparams->override_dbname;
159  }
160  keywords[i] = "fallback_application_name";
161  values[i++] = progname;
162  keywords[i] = NULL;
163  values[i++] = NULL;
164  Assert(i <= lengthof(keywords));
165 
166  new_pass = false;
167  AH->connection = PQconnectdbParams(keywords, values, true);
168 
169  if (!AH->connection)
170  pg_fatal("could not connect to database");
171 
172  if (PQstatus(AH->connection) == CONNECTION_BAD &&
174  password == NULL &&
175  prompt_password != TRI_NO)
176  {
177  PQfinish(AH->connection);
178  password = simple_prompt("Password: ", false);
179  new_pass = true;
180  }
181  } while (new_pass);
182 
183  /* check to see that the backend connection was successfully made */
184  if (PQstatus(AH->connection) == CONNECTION_BAD)
185  {
186  if (isReconnect)
187  pg_fatal("reconnection failed: %s",
189  else
190  pg_fatal("%s",
192  }
193 
194  /* Start strict; later phases may override this. */
197 
198  if (password && password != AH->savedPassword)
199  free(password);
200 
201  /*
202  * We want to remember connection's actual password, whether or not we got
203  * it by prompting. So we don't just store the password variable.
204  */
206  {
207  if (AH->savedPassword)
208  free(AH->savedPassword);
210  }
211 
212  /* check for version mismatch */
214 
216 
217  /* arrange for SIGINT to issue a query cancel on this connection */
219 }
220 
221 /*
222  * Close the connection to the database and also cancel off the query if we
223  * have one running.
224  */
225 void
227 {
228  ArchiveHandle *AH = (ArchiveHandle *) AHX;
229  char errbuf[1];
230 
231  if (!AH->connection)
232  return;
233 
234  if (AH->connCancel)
235  {
236  /*
237  * If we have an active query, send a cancel before closing, ignoring
238  * any errors. This is of no use for a normal exit, but might be
239  * helpful during pg_fatal().
240  */
242  (void) PQcancel(AH->connCancel, errbuf, sizeof(errbuf));
243 
244  /*
245  * Prevent signal handler from sending a cancel after this.
246  */
247  set_archive_cancel_info(AH, NULL);
248  }
249 
250  PQfinish(AH->connection);
251  AH->connection = NULL;
252 }
253 
254 PGconn *
256 {
257  ArchiveHandle *AH = (ArchiveHandle *) AHX;
258 
259  return AH->connection;
260 }
261 
262 static void
263 notice_processor(void *arg, const char *message)
264 {
265  pg_log_info("%s", message);
266 }
267 
268 /* Like pg_fatal(), but with a complaint about a particular query. */
269 static void
270 die_on_query_failure(ArchiveHandle *AH, const char *query)
271 {
272  pg_log_error("query failed: %s",
274  pg_log_error_detail("Query was: %s", query);
275  exit(1);
276 }
277 
278 void
279 ExecuteSqlStatement(Archive *AHX, const char *query)
280 {
281  ArchiveHandle *AH = (ArchiveHandle *) AHX;
282  PGresult *res;
283 
284  res = PQexec(AH->connection, query);
286  die_on_query_failure(AH, query);
287  PQclear(res);
288 }
289 
290 PGresult *
291 ExecuteSqlQuery(Archive *AHX, const char *query, ExecStatusType status)
292 {
293  ArchiveHandle *AH = (ArchiveHandle *) AHX;
294  PGresult *res;
295 
296  res = PQexec(AH->connection, query);
297  if (PQresultStatus(res) != status)
298  die_on_query_failure(AH, query);
299  return res;
300 }
301 
302 /*
303  * Execute an SQL query and verify that we got exactly one row back.
304  */
305 PGresult *
306 ExecuteSqlQueryForSingleRow(Archive *fout, const char *query)
307 {
308  PGresult *res;
309  int ntups;
310 
311  res = ExecuteSqlQuery(fout, query, PGRES_TUPLES_OK);
312 
313  /* Expecting a single result only */
314  ntups = PQntuples(res);
315  if (ntups != 1)
316  pg_fatal(ngettext("query returned %d row instead of one: %s",
317  "query returned %d rows instead of one: %s",
318  ntups),
319  ntups, query);
320 
321  return res;
322 }
323 
324 /*
325  * Convenience function to send a query.
326  * Monitors result to detect COPY statements
327  */
328 static void
329 ExecuteSqlCommand(ArchiveHandle *AH, const char *qry, const char *desc)
330 {
331  PGconn *conn = AH->connection;
332  PGresult *res;
333 
334 #ifdef NOT_USED
335  fprintf(stderr, "Executing: '%s'\n\n", qry);
336 #endif
337  res = PQexec(conn, qry);
338 
339  switch (PQresultStatus(res))
340  {
341  case PGRES_COMMAND_OK:
342  case PGRES_TUPLES_OK:
343  case PGRES_EMPTY_QUERY:
344  /* A-OK */
345  break;
346  case PGRES_COPY_IN:
347  /* Assume this is an expected result */
348  AH->pgCopyIn = true;
349  break;
350  default:
351  /* trouble */
352  warn_or_exit_horribly(AH, "%s: %sCommand was: %s",
353  desc, PQerrorMessage(conn), qry);
354  break;
355  }
356 
357  PQclear(res);
358 }
359 
360 
361 /*
362  * Process non-COPY table data (that is, INSERT commands).
363  *
364  * The commands have been run together as one long string for compressibility,
365  * and we are receiving them in bufferloads with arbitrary boundaries, so we
366  * have to locate command boundaries and save partial commands across calls.
367  * All state must be kept in AH->sqlparse, not in local variables of this
368  * routine. We assume that AH->sqlparse was filled with zeroes when created.
369  *
370  * We have to lex the data to the extent of identifying literals and quoted
371  * identifiers, so that we can recognize statement-terminating semicolons.
372  * We assume that INSERT data will not contain SQL comments, E'' literals,
373  * or dollar-quoted strings, so this is much simpler than a full SQL lexer.
374  *
375  * Note: when restoring from a pre-9.0 dump file, this code is also used to
376  * process BLOB COMMENTS data, which has the same problem of containing
377  * multiple SQL commands that might be split across bufferloads. Fortunately,
378  * that data won't contain anything complicated to lex either.
379  */
380 static void
381 ExecuteSimpleCommands(ArchiveHandle *AH, const char *buf, size_t bufLen)
382 {
383  const char *qry = buf;
384  const char *eos = buf + bufLen;
385 
386  /* initialize command buffer if first time through */
387  if (AH->sqlparse.curCmd == NULL)
389 
390  for (; qry < eos; qry++)
391  {
392  char ch = *qry;
393 
394  /* For neatness, we skip any newlines between commands */
395  if (!(ch == '\n' && AH->sqlparse.curCmd->len == 0))
397 
398  switch (AH->sqlparse.state)
399  {
400  case SQL_SCAN: /* Default state == 0, set in _allocAH */
401  if (ch == ';')
402  {
403  /*
404  * We've found the end of a statement. Send it and reset
405  * the buffer.
406  */
408  "could not execute query");
410  }
411  else if (ch == '\'')
412  {
414  AH->sqlparse.backSlash = false;
415  }
416  else if (ch == '"')
417  {
419  }
420  break;
421 
422  case SQL_IN_SINGLE_QUOTE:
423  /* We needn't handle '' specially */
424  if (ch == '\'' && !AH->sqlparse.backSlash)
425  AH->sqlparse.state = SQL_SCAN;
426  else if (ch == '\\' && !AH->public.std_strings)
428  else
429  AH->sqlparse.backSlash = false;
430  break;
431 
432  case SQL_IN_DOUBLE_QUOTE:
433  /* We needn't handle "" specially */
434  if (ch == '"')
435  AH->sqlparse.state = SQL_SCAN;
436  break;
437  }
438  }
439 }
440 
441 
442 /*
443  * Implement ahwrite() for direct-to-DB restore
444  */
445 int
446 ExecuteSqlCommandBuf(Archive *AHX, const char *buf, size_t bufLen)
447 {
448  ArchiveHandle *AH = (ArchiveHandle *) AHX;
449 
450  if (AH->outputKind == OUTPUT_COPYDATA)
451  {
452  /*
453  * COPY data.
454  *
455  * We drop the data on the floor if libpq has failed to enter COPY
456  * mode; this allows us to behave reasonably when trying to continue
457  * after an error in a COPY command.
458  */
459  if (AH->pgCopyIn &&
460  PQputCopyData(AH->connection, buf, bufLen) <= 0)
461  pg_fatal("error returned by PQputCopyData: %s",
463  }
464  else if (AH->outputKind == OUTPUT_OTHERDATA)
465  {
466  /*
467  * Table data expressed as INSERT commands; or, in old dump files,
468  * BLOB COMMENTS data (which is expressed as COMMENT ON commands).
469  */
470  ExecuteSimpleCommands(AH, buf, bufLen);
471  }
472  else
473  {
474  /*
475  * General SQL commands; we assume that commands will not be split
476  * across calls.
477  *
478  * In most cases the data passed to us will be a null-terminated
479  * string, but if it's not, we have to add a trailing null.
480  */
481  if (buf[bufLen] == '\0')
482  ExecuteSqlCommand(AH, buf, "could not execute query");
483  else
484  {
485  char *str = (char *) pg_malloc(bufLen + 1);
486 
487  memcpy(str, buf, bufLen);
488  str[bufLen] = '\0';
489  ExecuteSqlCommand(AH, str, "could not execute query");
490  free(str);
491  }
492  }
493 
494  return bufLen;
495 }
496 
497 /*
498  * Terminate a COPY operation during direct-to-DB restore
499  */
500 void
501 EndDBCopyMode(Archive *AHX, const char *tocEntryTag)
502 {
503  ArchiveHandle *AH = (ArchiveHandle *) AHX;
504 
505  if (AH->pgCopyIn)
506  {
507  PGresult *res;
508 
509  if (PQputCopyEnd(AH->connection, NULL) <= 0)
510  pg_fatal("error returned by PQputCopyEnd: %s",
512 
513  /* Check command status and return to normal libpq state */
514  res = PQgetResult(AH->connection);
516  warn_or_exit_horribly(AH, "COPY failed for table \"%s\": %s",
517  tocEntryTag, PQerrorMessage(AH->connection));
518  PQclear(res);
519 
520  /* Do this to ensure we've pumped libpq back to idle state */
521  if (PQgetResult(AH->connection) != NULL)
522  pg_log_warning("unexpected extra results during COPY of table \"%s\"",
523  tocEntryTag);
524 
525  AH->pgCopyIn = false;
526  }
527 }
528 
529 void
531 {
532  ArchiveHandle *AH = (ArchiveHandle *) AHX;
533 
534  ExecuteSqlCommand(AH, "BEGIN", "could not start database transaction");
535 }
536 
537 void
539 {
540  ArchiveHandle *AH = (ArchiveHandle *) AHX;
541 
542  ExecuteSqlCommand(AH, "COMMIT", "could not commit database transaction");
543 }
544 
545 void
547 {
548  /*
549  * If we are not restoring to a direct database connection, we have to
550  * guess about how to detect whether the blob exists. Assume new-style.
551  */
552  if (AH->connection == NULL ||
553  PQserverVersion(AH->connection) >= 90000)
554  {
555  ahprintf(AH,
556  "SELECT pg_catalog.lo_unlink(oid) "
557  "FROM pg_catalog.pg_largeobject_metadata "
558  "WHERE oid = '%u';\n",
559  oid);
560  }
561  else
562  {
563  /* Restoring to pre-9.0 server, so do it the old way */
564  ahprintf(AH,
565  "SELECT CASE WHEN EXISTS("
566  "SELECT 1 FROM pg_catalog.pg_largeobject WHERE loid = '%u'"
567  ") THEN pg_catalog.lo_unlink('%u') END;\n",
568  oid, oid);
569  }
570 }
void set_archive_cancel_info(ArchiveHandle *AH, PGconn *conn)
Definition: parallel.c:732
static Datum values[MAXATTR]
Definition: bootstrap.c:156
#define ngettext(s, p, n)
Definition: c.h:1179
#define lengthof(array)
Definition: c.h:734
#define ALWAYS_SECURE_SEARCH_PATH_SQL
Definition: connect.h:25
const char * PQparameterStatus(const PGconn *conn, const char *paramName)
Definition: fe-connect.c:6873
int PQserverVersion(const PGconn *conn)
Definition: fe-connect.c:6898
PGconn * PQconnectdbParams(const char *const *keywords, const char *const *values, int expand_dbname)
Definition: fe-connect.c:658
PGTransactionStatusType PQtransactionStatus(const PGconn *conn)
Definition: fe-connect.c:6863
int PQconnectionUsedPassword(const PGconn *conn)
Definition: fe-connect.c:6974
int PQconnectionNeedsPassword(const PGconn *conn)
Definition: fe-connect.c:6959
char * PQerrorMessage(const PGconn *conn)
Definition: fe-connect.c:6908
int PQcancel(PGcancel *cancel, char *errbuf, int errbufsize)
Definition: fe-connect.c:4481
ConnStatusType PQstatus(const PGconn *conn)
Definition: fe-connect.c:6855
void PQfinish(PGconn *conn)
Definition: fe-connect.c:4261
char * PQpass(const PGconn *conn)
Definition: fe-connect.c:6770
PQnoticeProcessor PQsetNoticeProcessor(PGconn *conn, PQnoticeProcessor proc, void *arg)
Definition: fe-connect.c:7076
ExecStatusType PQresultStatus(const PGresult *res)
Definition: fe-exec.c:3270
void PQclear(PGresult *res)
Definition: fe-exec.c:718
int PQputCopyEnd(PGconn *conn, const char *errormsg)
Definition: fe-exec.c:2683
int PQntuples(const PGresult *res)
Definition: fe-exec.c:3340
int PQputCopyData(PGconn *conn, const char *buffer, int nbytes)
Definition: fe-exec.c:2627
PGresult * PQexec(PGconn *conn, const char *query)
Definition: fe-exec.c:2273
char * PQgetvalue(const PGresult *res, int tup_num, int field_num)
Definition: fe-exec.c:3735
PGresult * PQgetResult(PGconn *conn)
Definition: fe-exec.c:2082
char * pg_strdup(const char *in)
Definition: fe_memutils.c:85
void * pg_malloc(size_t size)
Definition: fe_memutils.c:47
#define free(a)
Definition: header.h:65
int i
Definition: isn.c:73
@ CONNECTION_BAD
Definition: libpq-fe.h:61
ExecStatusType
Definition: libpq-fe.h:95
@ PGRES_COPY_IN
Definition: libpq-fe.h:104
@ PGRES_COMMAND_OK
Definition: libpq-fe.h:97
@ PGRES_EMPTY_QUERY
Definition: libpq-fe.h:96
@ PGRES_TUPLES_OK
Definition: libpq-fe.h:100
@ PQTRANS_ACTIVE
Definition: libpq-fe.h:119
Assert(fmt[strlen(fmt) - 1] !='\n')
exit(1)
#define pg_log_error(...)
Definition: logging.h:106
#define pg_log_info(...)
Definition: logging.h:124
#define pg_log_error_detail(...)
Definition: logging.h:109
const char * progname
Definition: main.c:50
void warn_or_exit_horribly(ArchiveHandle *AH, const char *fmt,...)
int ahprintf(ArchiveHandle *AH, const char *fmt,...)
@ SQL_IN_DOUBLE_QUOTE
@ SQL_IN_SINGLE_QUOTE
@ SQL_SCAN
@ OUTPUT_COPYDATA
@ OUTPUT_OTHERDATA
void ConnectDatabase(Archive *AHX, const ConnParams *cparams, bool isReconnect)
Definition: pg_backup_db.c:110
void ExecuteSqlStatement(Archive *AHX, const char *query)
Definition: pg_backup_db.c:279
void DropBlobIfExists(ArchiveHandle *AH, Oid oid)
Definition: pg_backup_db.c:546
void EndDBCopyMode(Archive *AHX, const char *tocEntryTag)
Definition: pg_backup_db.c:501
static void die_on_query_failure(ArchiveHandle *AH, const char *query)
Definition: pg_backup_db.c:270
PGresult * ExecuteSqlQuery(Archive *AHX, const char *query, ExecStatusType status)
Definition: pg_backup_db.c:291
static void _check_database_version(ArchiveHandle *AH)
Definition: pg_backup_db.c:33
PGconn * GetConnection(Archive *AHX)
Definition: pg_backup_db.c:255
void ReconnectToServer(ArchiveHandle *AH, const char *dbname)
Definition: pg_backup_db.c:74
static void ExecuteSimpleCommands(ArchiveHandle *AH, const char *buf, size_t bufLen)
Definition: pg_backup_db.c:381
void StartTransaction(Archive *AHX)
Definition: pg_backup_db.c:530
int ExecuteSqlCommandBuf(Archive *AHX, const char *buf, size_t bufLen)
Definition: pg_backup_db.c:446
static void ExecuteSqlCommand(ArchiveHandle *AH, const char *qry, const char *desc)
Definition: pg_backup_db.c:329
static void notice_processor(void *arg, const char *message)
Definition: pg_backup_db.c:263
void DisconnectDatabase(Archive *AHX)
Definition: pg_backup_db.c:226
void CommitTransaction(Archive *AHX)
Definition: pg_backup_db.c:538
PGresult * ExecuteSqlQueryForSingleRow(Archive *fout, const char *query)
Definition: pg_backup_db.c:306
void * arg
#define pg_fatal(...)
static void static void status(const char *fmt,...) pg_attribute_printf(1
Definition: pg_regress.c:229
static char * buf
Definition: pg_test_fsync.c:67
#define pg_log_warning(...)
Definition: pgfnames.c:24
#define fprintf
Definition: port.h:229
unsigned int Oid
Definition: postgres_ext.h:31
PQExpBuffer createPQExpBuffer(void)
Definition: pqexpbuffer.c:74
void resetPQExpBuffer(PQExpBuffer str)
Definition: pqexpbuffer.c:148
void appendPQExpBufferChar(PQExpBuffer str, char ch)
Definition: pqexpbuffer.c:380
char * simple_prompt(const char *prompt, bool echo)
Definition: sprompt.c:38
static char * password
Definition: streamutil.c:53
char * dbname
Definition: streamutil.c:51
PGconn * conn
Definition: streamutil.c:54
int remoteVersion
Definition: pg_backup.h:214
char * remoteVersionStr
Definition: pg_backup.h:213
bool isStandby
Definition: pg_backup.h:215
int maxRemoteVersion
Definition: pg_backup.h:218
bool std_strings
Definition: pg_backup.h:225
RestoreOptions * ropt
Definition: pg_backup.h:210
PGcancel *volatile connCancel
sqlparseInfo sqlparse
ArchiverOutput outputKind
char * override_dbname
Definition: pg_backup.h:89
char * pgport
Definition: pg_backup.h:83
char * pghost
Definition: pg_backup.h:84
trivalue promptPassword
Definition: pg_backup.h:86
char * username
Definition: pg_backup.h:85
char * dbname
Definition: pg_backup.h:82
ConnParams cparams
Definition: pg_backup.h:142
PQExpBuffer curCmd
sqlparseState state
trivalue
Definition: vacuumlo.c:35
@ TRI_YES
Definition: vacuumlo.c:38
@ TRI_NO
Definition: vacuumlo.c:37