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