granicus.if.org Git - postgresql/blob - src/bin/pg_dump/pg_backup_db.c

   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 "dumputils.h"
  15 #include "fe_utils/string_utils.h"
  16 #include "parallel.h"
  17 #include "pg_backup_archiver.h"
  18 #include "pg_backup_db.h"
  19 #include "pg_backup_utils.h"
  20
  21 #include <unistd.h>
  22 #include <ctype.h>
  23 #ifdef HAVE_TERMIOS_H
  24 #include <termios.h>
  25 #endif
  26
  27
  28 /* translator: this is a module name */
  29 static const char *modulename = gettext_noop("archiver (db)");
  30
  31 static void _check_database_version(ArchiveHandle *AH);
  32 static PGconn *_connectDB(ArchiveHandle *AH, const char *newdbname, const char *newUser);
  33 static void notice_processor(void *arg, const char *message);
  34
  35 static void
  36 _check_database_version(ArchiveHandle *AH)
  37 {
  38         const char *remoteversion_str;
  39         int                     remoteversion;
  40         PGresult   *res;
  41
  42         remoteversion_str = PQparameterStatus(AH->connection, "server_version");
  43         remoteversion = PQserverVersion(AH->connection);
  44         if (remoteversion == 0 || !remoteversion_str)
  45                 exit_horribly(modulename, "could not get server_version from libpq\n");
  46
  47         AH->public.remoteVersionStr = pg_strdup(remoteversion_str);
  48         AH->public.remoteVersion = remoteversion;
  49         if (!AH->archiveRemoteVersion)
  50                 AH->archiveRemoteVersion = AH->public.remoteVersionStr;
  51
  52         if (remoteversion != PG_VERSION_NUM
  53                 && (remoteversion < AH->public.minRemoteVersion ||
  54                         remoteversion > AH->public.maxRemoteVersion))
  55         {
  56                 write_msg(NULL, "server version: %s; %s version: %s\n",
  57                                   remoteversion_str, progname, PG_VERSION);
  58                 exit_horribly(NULL, "aborting because of server version mismatch\n");
  59         }
  60
  61         /*
  62          * When running against 9.0 or later, check if we are in recovery mode,
  63          * which means we are on a hot standby.
  64          */
  65         if (remoteversion >= 90000)
  66         {
  67                 res = ExecuteSqlQueryForSingleRow((Archive *) AH, "SELECT pg_catalog.pg_is_in_recovery()");
  68
  69                 AH->public.isStandby = (strcmp(PQgetvalue(res, 0, 0), "t") == 0);
  70                 PQclear(res);
  71         }
  72         else
  73                 AH->public.isStandby = false;
  74 }
  75
  76 /*
  77  * Reconnect to the server.  If dbname is not NULL, use that database,
  78  * else the one associated with the archive handle.  If username is
  79  * not NULL, use that user name, else the one from the handle.
  80  */
  81 void
  82 ReconnectToServer(ArchiveHandle *AH, const char *dbname, const char *username)
  83 {
  84         PGconn     *newConn;
  85         const char *newdbname;
  86         const char *newusername;
  87
  88         if (!dbname)
  89                 newdbname = PQdb(AH->connection);
  90         else
  91                 newdbname = dbname;
  92
  93         if (!username)
  94                 newusername = PQuser(AH->connection);
  95         else
  96                 newusername = username;
  97
  98         newConn = _connectDB(AH, newdbname, newusername);
  99
 100         /* Update ArchiveHandle's connCancel before closing old connection */
 101         set_archive_cancel_info(AH, newConn);
 102
 103         PQfinish(AH->connection);
 104         AH->connection = newConn;
 105 }
 106
 107 /*
 108  * Connect to the db again.
 109  *
 110  * Note: it's not really all that sensible to use a single-entry password
 111  * cache if the username keeps changing.  In current usage, however, the
 112  * username never does change, so one savedPassword is sufficient.  We do
 113  * update the cache on the off chance that the password has changed since the
 114  * start of the run.
 115  */
 116 static PGconn *
 117 _connectDB(ArchiveHandle *AH, const char *reqdb, const char *requser)
 118 {
 119         PQExpBufferData connstr;
 120         PGconn     *newConn;
 121         const char *newdb;
 122         const char *newuser;
 123         char       *password;
 124         char            passbuf[100];
 125         bool            new_pass;
 126
 127         if (!reqdb)
 128                 newdb = PQdb(AH->connection);
 129         else
 130                 newdb = reqdb;
 131
 132         if (!requser || strlen(requser) == 0)
 133                 newuser = PQuser(AH->connection);
 134         else
 135                 newuser = requser;
 136
 137         ahlog(AH, 1, "connecting to database \"%s\" as user \"%s\"\n",
 138                   newdb, newuser);
 139
 140         password = AH->savedPassword;
 141
 142         if (AH->promptPassword == TRI_YES && password == NULL)
 143         {
 144                 simple_prompt("Password: ", passbuf, sizeof(passbuf), false);
 145                 password = passbuf;
 146         }
 147
 148         initPQExpBuffer(&connstr);
 149         appendPQExpBuffer(&connstr, "dbname=");
 150         appendConnStrVal(&connstr, newdb);
 151
 152         do
 153         {
 154                 const char *keywords[7];
 155                 const char *values[7];
 156
 157                 keywords[0] = "host";
 158                 values[0] = PQhost(AH->connection);
 159                 keywords[1] = "port";
 160                 values[1] = PQport(AH->connection);
 161                 keywords[2] = "user";
 162                 values[2] = newuser;
 163                 keywords[3] = "password";
 164                 values[3] = password;
 165                 keywords[4] = "dbname";
 166                 values[4] = connstr.data;
 167                 keywords[5] = "fallback_application_name";
 168                 values[5] = progname;
 169                 keywords[6] = NULL;
 170                 values[6] = NULL;
 171
 172                 new_pass = false;
 173                 newConn = PQconnectdbParams(keywords, values, true);
 174
 175                 if (!newConn)
 176                         exit_horribly(modulename, "failed to reconnect to database\n");
 177
 178                 if (PQstatus(newConn) == CONNECTION_BAD)
 179                 {
 180                         if (!PQconnectionNeedsPassword(newConn))
 181                                 exit_horribly(modulename, "could not reconnect to database: %s",
 182                                                           PQerrorMessage(newConn));
 183                         PQfinish(newConn);
 184
 185                         if (password)
 186                                 fprintf(stderr, "Password incorrect\n");
 187
 188                         fprintf(stderr, "Connecting to %s as %s\n",
 189                                         newdb, newuser);
 190
 191                         if (AH->promptPassword != TRI_NO)
 192                         {
 193                                 simple_prompt("Password: ", passbuf, sizeof(passbuf), false);
 194                                 password = passbuf;
 195                         }
 196                         else
 197                                 exit_horribly(modulename, "connection needs password\n");
 198
 199                         new_pass = true;
 200                 }
 201         } while (new_pass);
 202
 203         /*
 204          * We want to remember connection's actual password, whether or not we got
 205          * it by prompting.  So we don't just store the password variable.
 206          */
 207         if (PQconnectionUsedPassword(newConn))
 208         {
 209                 if (AH->savedPassword)
 210                         free(AH->savedPassword);
 211                 AH->savedPassword = pg_strdup(PQpass(newConn));
 212         }
 213
 214         termPQExpBuffer(&connstr);
 215
 216         /* check for version mismatch */
 217         _check_database_version(AH);
 218
 219         PQsetNoticeProcessor(newConn, notice_processor, NULL);
 220
 221         return newConn;
 222 }
 223
 224
 225 /*
 226  * Make a database connection with the given parameters.  The
 227  * connection handle is returned, the parameters are stored in AHX.
 228  * An interactive password prompt is automatically issued if required.
 229  *
 230  * Note: it's not really all that sensible to use a single-entry password
 231  * cache if the username keeps changing.  In current usage, however, the
 232  * username never does change, so one savedPassword is sufficient.
 233  */
 234 void
 235 ConnectDatabase(Archive *AHX,
 236                                 const char *dbname,
 237                                 const char *pghost,
 238                                 const char *pgport,
 239                                 const char *username,
 240                                 trivalue prompt_password)
 241 {
 242         ArchiveHandle *AH = (ArchiveHandle *) AHX;
 243         char       *password;
 244         char            passbuf[100];
 245         bool            new_pass;
 246
 247         if (AH->connection)
 248                 exit_horribly(modulename, "already connected to a database\n");
 249
 250         password = AH->savedPassword;
 251
 252         if (prompt_password == TRI_YES && password == NULL)
 253         {
 254                 simple_prompt("Password: ", passbuf, sizeof(passbuf), false);
 255                 password = passbuf;
 256         }
 257         AH->promptPassword = prompt_password;
 258
 259         /*
 260          * Start the connection.  Loop until we have a password if requested by
 261          * backend.
 262          */
 263         do
 264         {
 265                 const char *keywords[7];
 266                 const char *values[7];
 267
 268                 keywords[0] = "host";
 269                 values[0] = pghost;
 270                 keywords[1] = "port";
 271                 values[1] = pgport;
 272                 keywords[2] = "user";
 273                 values[2] = username;
 274                 keywords[3] = "password";
 275                 values[3] = password;
 276                 keywords[4] = "dbname";
 277                 values[4] = dbname;
 278                 keywords[5] = "fallback_application_name";
 279                 values[5] = progname;
 280                 keywords[6] = NULL;
 281                 values[6] = NULL;
 282
 283                 new_pass = false;
 284                 AH->connection = PQconnectdbParams(keywords, values, true);
 285
 286                 if (!AH->connection)
 287                         exit_horribly(modulename, "failed to connect to database\n");
 288
 289                 if (PQstatus(AH->connection) == CONNECTION_BAD &&
 290                         PQconnectionNeedsPassword(AH->connection) &&
 291                         password == NULL &&
 292                         prompt_password != TRI_NO)
 293                 {
 294                         PQfinish(AH->connection);
 295                         simple_prompt("Password: ", passbuf, sizeof(passbuf), false);
 296                         password = passbuf;
 297                         new_pass = true;
 298                 }
 299         } while (new_pass);
 300
 301         /* check to see that the backend connection was successfully made */
 302         if (PQstatus(AH->connection) == CONNECTION_BAD)
 303                 exit_horribly(modulename, "connection to database \"%s\" failed: %s",
 304                                           PQdb(AH->connection) ? PQdb(AH->connection) : "",
 305                                           PQerrorMessage(AH->connection));
 306
 307         /*
 308          * We want to remember connection's actual password, whether or not we got
 309          * it by prompting.  So we don't just store the password variable.
 310          */
 311         if (PQconnectionUsedPassword(AH->connection))
 312         {
 313                 if (AH->savedPassword)
 314                         free(AH->savedPassword);
 315                 AH->savedPassword = pg_strdup(PQpass(AH->connection));
 316         }
 317
 318         /* check for version mismatch */
 319         _check_database_version(AH);
 320
 321         PQsetNoticeProcessor(AH->connection, notice_processor, NULL);
 322
 323         /* arrange for SIGINT to issue a query cancel on this connection */
 324         set_archive_cancel_info(AH, AH->connection);
 325 }
 326
 327 /*
 328  * Close the connection to the database and also cancel off the query if we
 329  * have one running.
 330  */
 331 void
 332 DisconnectDatabase(Archive *AHX)
 333 {
 334         ArchiveHandle *AH = (ArchiveHandle *) AHX;
 335         char            errbuf[1];
 336
 337         if (!AH->connection)
 338                 return;
 339
 340         if (AH->connCancel)
 341         {
 342                 /*
 343                  * If we have an active query, send a cancel before closing, ignoring
 344                  * any errors.  This is of no use for a normal exit, but might be
 345                  * helpful during exit_horribly().
 346                  */
 347                 if (PQtransactionStatus(AH->connection) == PQTRANS_ACTIVE)
 348                         (void) PQcancel(AH->connCancel, errbuf, sizeof(errbuf));
 349
 350                 /*
 351                  * Prevent signal handler from sending a cancel after this.
 352                  */
 353                 set_archive_cancel_info(AH, NULL);
 354         }
 355
 356         PQfinish(AH->connection);
 357         AH->connection = NULL;
 358 }
 359
 360 PGconn *
 361 GetConnection(Archive *AHX)
 362 {
 363         ArchiveHandle *AH = (ArchiveHandle *) AHX;
 364
 365         return AH->connection;
 366 }
 367
 368 static void
 369 notice_processor(void *arg, const char *message)
 370 {
 371         write_msg(NULL, "%s", message);
 372 }
 373
 374 /* Like exit_horribly(), but with a complaint about a particular query. */
 375 static void
 376 die_on_query_failure(ArchiveHandle *AH, const char *modulename, const char *query)
 377 {
 378         write_msg(modulename, "query failed: %s",
 379                           PQerrorMessage(AH->connection));
 380         exit_horribly(modulename, "query was: %s\n", query);
 381 }
 382
 383 void
 384 ExecuteSqlStatement(Archive *AHX, const char *query)
 385 {
 386         ArchiveHandle *AH = (ArchiveHandle *) AHX;
 387         PGresult   *res;
 388
 389         res = PQexec(AH->connection, query);
 390         if (PQresultStatus(res) != PGRES_COMMAND_OK)
 391                 die_on_query_failure(AH, modulename, query);
 392         PQclear(res);
 393 }
 394
 395 PGresult *
 396 ExecuteSqlQuery(Archive *AHX, const char *query, ExecStatusType status)
 397 {
 398         ArchiveHandle *AH = (ArchiveHandle *) AHX;
 399         PGresult   *res;
 400
 401         res = PQexec(AH->connection, query);
 402         if (PQresultStatus(res) != status)
 403                 die_on_query_failure(AH, modulename, query);
 404         return res;
 405 }
 406
 407 /*
 408  * Execute an SQL query and verify that we got exactly one row back.
 409  */
 410 PGresult *
 411 ExecuteSqlQueryForSingleRow(Archive *fout, const char *query)
 412 {
 413         PGresult   *res;
 414         int                     ntups;
 415
 416         res = ExecuteSqlQuery(fout, query, PGRES_TUPLES_OK);
 417
 418         /* Expecting a single result only */
 419         ntups = PQntuples(res);
 420         if (ntups != 1)
 421                 exit_horribly(NULL,
 422                                           ngettext("query returned %d row instead of one: %s\n",
 423                                                            "query returned %d rows instead of one: %s\n",
 424                                                            ntups),
 425                                           ntups, query);
 426
 427         return res;
 428 }
 429
 430 /*
 431  * Convenience function to send a query.
 432  * Monitors result to detect COPY statements
 433  */
 434 static void
 435 ExecuteSqlCommand(ArchiveHandle *AH, const char *qry, const char *desc)
 436 {
 437         PGconn     *conn = AH->connection;
 438         PGresult   *res;
 439
 440 #ifdef NOT_USED
 441         fprintf(stderr, "Executing: '%s'\n\n", qry);
 442 #endif
 443         res = PQexec(conn, qry);
 444
 445         switch (PQresultStatus(res))
 446         {
 447                 case PGRES_COMMAND_OK:
 448                 case PGRES_TUPLES_OK:
 449                 case PGRES_EMPTY_QUERY:
 450                         /* A-OK */
 451                         break;
 452                 case PGRES_COPY_IN:
 453                         /* Assume this is an expected result */
 454                         AH->pgCopyIn = true;
 455                         break;
 456                 default:
 457                         /* trouble */
 458                         warn_or_exit_horribly(AH, modulename, "%s: %s    Command was: %s\n",
 459                                                                   desc, PQerrorMessage(conn), qry);
 460                         break;
 461         }
 462
 463         PQclear(res);
 464 }
 465
 466
 467 /*
 468  * Process non-COPY table data (that is, INSERT commands).
 469  *
 470  * The commands have been run together as one long string for compressibility,
 471  * and we are receiving them in bufferloads with arbitrary boundaries, so we
 472  * have to locate command boundaries and save partial commands across calls.
 473  * All state must be kept in AH->sqlparse, not in local variables of this
 474  * routine.  We assume that AH->sqlparse was filled with zeroes when created.
 475  *
 476  * We have to lex the data to the extent of identifying literals and quoted
 477  * identifiers, so that we can recognize statement-terminating semicolons.
 478  * We assume that INSERT data will not contain SQL comments, E'' literals,
 479  * or dollar-quoted strings, so this is much simpler than a full SQL lexer.
 480  *
 481  * Note: when restoring from a pre-9.0 dump file, this code is also used to
 482  * process BLOB COMMENTS data, which has the same problem of containing
 483  * multiple SQL commands that might be split across bufferloads.  Fortunately,
 484  * that data won't contain anything complicated to lex either.
 485  */
 486 static void
 487 ExecuteSimpleCommands(ArchiveHandle *AH, const char *buf, size_t bufLen)
 488 {
 489         const char *qry = buf;
 490         const char *eos = buf + bufLen;
 491
 492         /* initialize command buffer if first time through */
 493         if (AH->sqlparse.curCmd == NULL)
 494                 AH->sqlparse.curCmd = createPQExpBuffer();
 495
 496         for (; qry < eos; qry++)
 497         {
 498                 char            ch = *qry;
 499
 500                 /* For neatness, we skip any newlines between commands */
 501                 if (!(ch == '\n' && AH->sqlparse.curCmd->len == 0))
 502                         appendPQExpBufferChar(AH->sqlparse.curCmd, ch);
 503
 504                 switch (AH->sqlparse.state)
 505                 {
 506                         case SQL_SCAN:          /* Default state == 0, set in _allocAH */
 507                                 if (ch == ';')
 508                                 {
 509                                         /*
 510                                          * We've found the end of a statement. Send it and reset
 511                                          * the buffer.
 512                                          */
 513                                         ExecuteSqlCommand(AH, AH->sqlparse.curCmd->data,
 514                                                                           "could not execute query");
 515                                         resetPQExpBuffer(AH->sqlparse.curCmd);
 516                                 }
 517                                 else if (ch == '\'')
 518                                 {
 519                                         AH->sqlparse.state = SQL_IN_SINGLE_QUOTE;
 520                                         AH->sqlparse.backSlash = false;
 521                                 }
 522                                 else if (ch == '"')
 523                                 {
 524                                         AH->sqlparse.state = SQL_IN_DOUBLE_QUOTE;
 525                                 }
 526                                 break;
 527
 528                         case SQL_IN_SINGLE_QUOTE:
 529                                 /* We needn't handle '' specially */
 530                                 if (ch == '\'' && !AH->sqlparse.backSlash)
 531                                         AH->sqlparse.state = SQL_SCAN;
 532                                 else if (ch == '\\' && !AH->public.std_strings)
 533                                         AH->sqlparse.backSlash = !AH->sqlparse.backSlash;
 534                                 else
 535                                         AH->sqlparse.backSlash = false;
 536                                 break;
 537
 538                         case SQL_IN_DOUBLE_QUOTE:
 539                                 /* We needn't handle "" specially */
 540                                 if (ch == '"')
 541                                         AH->sqlparse.state = SQL_SCAN;
 542                                 break;
 543                 }
 544         }
 545 }
 546
 547
 548 /*
 549  * Implement ahwrite() for direct-to-DB restore
 550  */
 551 int
 552 ExecuteSqlCommandBuf(Archive *AHX, const char *buf, size_t bufLen)
 553 {
 554         ArchiveHandle *AH = (ArchiveHandle *) AHX;
 555
 556         if (AH->outputKind == OUTPUT_COPYDATA)
 557         {
 558                 /*
 559                  * COPY data.
 560                  *
 561                  * We drop the data on the floor if libpq has failed to enter COPY
 562                  * mode; this allows us to behave reasonably when trying to continue
 563                  * after an error in a COPY command.
 564                  */
 565                 if (AH->pgCopyIn &&
 566                         PQputCopyData(AH->connection, buf, bufLen) <= 0)
 567                         exit_horribly(modulename, "error returned by PQputCopyData: %s",
 568                                                   PQerrorMessage(AH->connection));
 569         }
 570         else if (AH->outputKind == OUTPUT_OTHERDATA)
 571         {
 572                 /*
 573                  * Table data expressed as INSERT commands; or, in old dump files,
 574                  * BLOB COMMENTS data (which is expressed as COMMENT ON commands).
 575                  */
 576                 ExecuteSimpleCommands(AH, buf, bufLen);
 577         }
 578         else
 579         {
 580                 /*
 581                  * General SQL commands; we assume that commands will not be split
 582                  * across calls.
 583                  *
 584                  * In most cases the data passed to us will be a null-terminated
 585                  * string, but if it's not, we have to add a trailing null.
 586                  */
 587                 if (buf[bufLen] == '\0')
 588                         ExecuteSqlCommand(AH, buf, "could not execute query");
 589                 else
 590                 {
 591                         char       *str = (char *) pg_malloc(bufLen + 1);
 592
 593                         memcpy(str, buf, bufLen);
 594                         str[bufLen] = '\0';
 595                         ExecuteSqlCommand(AH, str, "could not execute query");
 596                         free(str);
 597                 }
 598         }
 599
 600         return bufLen;
 601 }
 602
 603 /*
 604  * Terminate a COPY operation during direct-to-DB restore
 605  */
 606 void
 607 EndDBCopyMode(Archive *AHX, const char *tocEntryTag)
 608 {
 609         ArchiveHandle *AH = (ArchiveHandle *) AHX;
 610
 611         if (AH->pgCopyIn)
 612         {
 613                 PGresult   *res;
 614
 615                 if (PQputCopyEnd(AH->connection, NULL) <= 0)
 616                         exit_horribly(modulename, "error returned by PQputCopyEnd: %s",
 617                                                   PQerrorMessage(AH->connection));
 618
 619                 /* Check command status and return to normal libpq state */
 620                 res = PQgetResult(AH->connection);
 621                 if (PQresultStatus(res) != PGRES_COMMAND_OK)
 622                         warn_or_exit_horribly(AH, modulename, "COPY failed for table \"%s\": %s",
 623                                                                   tocEntryTag, PQerrorMessage(AH->connection));
 624                 PQclear(res);
 625
 626                 /* Do this to ensure we've pumped libpq back to idle state */
 627                 if (PQgetResult(AH->connection) != NULL)
 628                         write_msg(NULL, "WARNING: unexpected extra results during COPY of table \"%s\"\n",
 629                                           tocEntryTag);
 630
 631                 AH->pgCopyIn = false;
 632         }
 633 }
 634
 635 void
 636 StartTransaction(Archive *AHX)
 637 {
 638         ArchiveHandle *AH = (ArchiveHandle *) AHX;
 639
 640         ExecuteSqlCommand(AH, "BEGIN", "could not start database transaction");
 641 }
 642
 643 void
 644 CommitTransaction(Archive *AHX)
 645 {
 646         ArchiveHandle *AH = (ArchiveHandle *) AHX;
 647
 648         ExecuteSqlCommand(AH, "COMMIT", "could not commit database transaction");
 649 }
 650
 651 void
 652 DropBlobIfExists(ArchiveHandle *AH, Oid oid)
 653 {
 654         /*
 655          * If we are not restoring to a direct database connection, we have to
 656          * guess about how to detect whether the blob exists.  Assume new-style.
 657          */
 658         if (AH->connection == NULL ||
 659                 PQserverVersion(AH->connection) >= 90000)
 660         {
 661                 ahprintf(AH,
 662                                  "SELECT pg_catalog.lo_unlink(oid) "
 663                                  "FROM pg_catalog.pg_largeobject_metadata "
 664                                  "WHERE oid = '%u';\n",
 665                                  oid);
 666         }
 667         else
 668         {
 669                 /* Restoring to pre-9.0 server, so do it the old way */
 670                 ahprintf(AH,
 671                                  "SELECT CASE WHEN EXISTS("
 672                                  "SELECT 1 FROM pg_catalog.pg_largeobject WHERE loid = '%u'"
 673                                  ") THEN pg_catalog.lo_unlink('%u') END;\n",
 674                                  oid, oid);
 675         }
 676 }