2 Bacula® - The Network Backup Solution
4 Copyright (C) 2003-2007 Free Software Foundation Europe e.V.
6 The main author of Bacula is Kern Sibbald, with contributions from
7 many others, a complete list can be found in the file AUTHORS.
8 This program is Free Software; you can redistribute it and/or
9 modify it under the terms of version two of the GNU General Public
10 License as published by the Free Software Foundation and included
13 This program is distributed in the hope that it will be useful, but
14 WITHOUT ANY WARRANTY; without even the implied warranty of
15 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
16 General Public License for more details.
18 You should have received a copy of the GNU General Public License
19 along with this program; if not, write to the Free Software
20 Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
23 Bacula® is a registered trademark of Kern Sibbald.
24 The licensor of Bacula is the Free Software Foundation Europe
25 (FSFE), Fiduciary Program, Sumatrastrasse 25, 8006 Zürich,
26 Switzerland, email:ftf@fsfeurope.org.
29 * Bacula Catalog Database routines specific to PostgreSQL
30 * These are PostgreSQL specific routines
32 * Dan Langille, December 2003
33 * based upon work done by Kern Sibbald, March 2000
39 /* The following is necessary so that we do not include
40 * the dummy external definition of DB.
42 #define __SQL_C /* indicate that this is sql.c */
47 #ifdef HAVE_POSTGRESQL
49 #include "postgres_ext.h" /* needed for NAMEDATALEN */
50 #include "pg_config_manual.h" /* get NAMEDATALEN on version 8.3 or later */
52 /* -----------------------------------------------------------------------
54 * PostgreSQL dependent defines and subroutines
56 * -----------------------------------------------------------------------
59 /* List of open databases */
60 static BQUEUE db_list = {&db_list, &db_list};
62 static pthread_mutex_t mutex = PTHREAD_MUTEX_INITIALIZER;
65 * Retrieve database type
75 * Initialize database data structure. In principal this should
76 * never have errors, or it is really fatal.
79 db_init_database(JCR *jcr, const char *db_name, const char *db_user, const char *db_password,
80 const char *db_address, int db_port, const char *db_socket,
81 int mult_db_connections)
86 Jmsg(jcr, M_FATAL, 0, _("A user name for PostgreSQL must be supplied.\n"));
89 P(mutex); /* lock DB queue */
90 if (!mult_db_connections) {
91 /* Look to see if DB already open */
92 for (mdb=NULL; (mdb=(B_DB *)qnext(&db_list, &mdb->bq)); ) {
93 if (bstrcmp(mdb->db_name, db_name) &&
94 bstrcmp(mdb->db_address, db_address) &&
95 mdb->db_port == db_port) {
96 Dmsg2(100, "DB REopen %d %s\n", mdb->ref_count, db_name);
99 return mdb; /* already open */
103 Dmsg0(100, "db_open first time\n");
104 mdb = (B_DB *)malloc(sizeof(B_DB));
105 memset(mdb, 0, sizeof(B_DB));
106 mdb->db_name = bstrdup(db_name);
107 mdb->db_user = bstrdup(db_user);
109 mdb->db_password = bstrdup(db_password);
112 mdb->db_address = bstrdup(db_address);
115 mdb->db_socket = bstrdup(db_socket);
117 mdb->db_port = db_port;
118 mdb->have_insert_id = TRUE;
119 mdb->errmsg = get_pool_memory(PM_EMSG); /* get error message buffer */
121 mdb->cmd = get_pool_memory(PM_EMSG); /* get command buffer */
122 mdb->cached_path = get_pool_memory(PM_FNAME);
123 mdb->cached_path_id = 0;
125 mdb->fname = get_pool_memory(PM_FNAME);
126 mdb->path = get_pool_memory(PM_FNAME);
127 mdb->esc_name = get_pool_memory(PM_FNAME);
128 mdb->esc_path = get_pool_memory(PM_FNAME);
129 mdb->allow_transactions = mult_db_connections;
130 qinsert(&db_list, &mdb->bq); /* put db in list */
135 /* Check that the database correspond to the encoding we want */
136 static bool check_database_encoding(JCR *jcr, B_DB *mdb)
141 if (!db_sql_query(mdb, "SELECT getdatabaseencoding()", NULL, NULL)) {
142 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
146 if ((row = sql_fetch_row(mdb)) == NULL) {
147 Mmsg1(mdb->errmsg, _("error fetching row: %s\n"), sql_strerror(mdb));
148 Jmsg(jcr, M_ERROR, 0, "Can't check database encoding %s", mdb->errmsg);
150 ret = bstrcmp(row[0], "SQL_ASCII");
153 /* if we are in SQL_ASCII, we can force the client_encoding to SQL_ASCII too */
154 db_sql_query(mdb, "SET client_encoding TO 'SQL_ASCII'", NULL, NULL);
156 } else { /* something is wrong with database encoding */
158 _("Encoding error for database \"%s\". Wanted SQL_ASCII, got %s\n"),
159 mdb->db_name, row[0]);
160 Jmsg(jcr, M_WARNING, 0, "%s", mdb->errmsg);
161 Dmsg1(50, "%s", mdb->errmsg);
168 * Now actually open the database. This can generate errors,
169 * which are returned in the errmsg
171 * DO NOT close the database or free(mdb) here !!!!
174 db_open_database(JCR *jcr, B_DB *mdb)
179 #ifdef xxx /* require libpq >= 8.2 */
180 if (!PQisthreadsafe()) {
181 Jmsg(jcr, M_ABORT, 0, _("PostgreSQL configuration problem. "
182 "PostgreSQL library is not thread safe. Cannot continue.\n"));
186 if (mdb->connected) {
190 mdb->connected = false;
192 if ((errstat=rwl_init(&mdb->lock)) != 0) {
194 Mmsg1(&mdb->errmsg, _("Unable to initialize DB lock. ERR=%s\n"),
195 be.bstrerror(errstat));
201 bsnprintf(buf, sizeof(buf), "%d", mdb->db_port);
207 /* If connection fails, try at 5 sec intervals for 30 seconds. */
208 for (int retry=0; retry < 6; retry++) {
209 /* connect to the database */
210 mdb->db = PQsetdbLogin(
211 mdb->db_address, /* default = localhost */
212 port, /* default port */
213 NULL, /* pg options */
214 NULL, /* tty, ignored */
215 mdb->db_name, /* database name */
216 mdb->db_user, /* login name */
217 mdb->db_password); /* password */
219 /* If no connect, try once more in case it is a timing problem */
220 if (PQstatus(mdb->db) == CONNECTION_OK) {
226 Dmsg0(50, "pg_real_connect done\n");
227 Dmsg3(50, "db_user=%s db_name=%s db_password=%s\n", mdb->db_user, mdb->db_name,
228 mdb->db_password==NULL?"(NULL)":mdb->db_password);
230 if (PQstatus(mdb->db) != CONNECTION_OK) {
231 Mmsg2(&mdb->errmsg, _("Unable to connect to PostgreSQL server. Database=%s User=%s\n"
232 "Possible causes: SQL server not running; password incorrect; max_connections exceeded.\n"),
233 mdb->db_name, mdb->db_user);
238 mdb->connected = true;
240 if (!check_tables_version(jcr, mdb)) {
245 sql_query(mdb, "SET datestyle TO 'ISO, YMD'");
247 /* tell PostgreSQL we are using standard conforming strings
248 and avoid warnings such as:
249 WARNING: nonstandard use of \\ in a string literal
251 sql_query(mdb, "set standard_conforming_strings=on");
253 /* check that encoding is SQL_ASCII */
254 check_database_encoding(jcr, mdb);
261 db_close_database(JCR *jcr, B_DB *mdb)
266 db_end_transaction(jcr, mdb);
268 sql_free_result(mdb);
270 if (mdb->ref_count == 0) {
272 if (mdb->connected && mdb->db) {
275 rwl_destroy(&mdb->lock);
276 free_pool_memory(mdb->errmsg);
277 free_pool_memory(mdb->cmd);
278 free_pool_memory(mdb->cached_path);
279 free_pool_memory(mdb->fname);
280 free_pool_memory(mdb->path);
281 free_pool_memory(mdb->esc_name);
282 free_pool_memory(mdb->esc_path);
289 if (mdb->db_password) {
290 free(mdb->db_password);
292 if (mdb->db_address) {
293 free(mdb->db_address);
295 if (mdb->db_socket) {
296 free(mdb->db_socket);
303 void db_check_backend_thread_safe()
305 #ifdef HAVE_BATCH_FILE_INSERT
306 if (!PQisthreadsafe()) {
307 Emsg0(M_ABORT, 0, _("Pg client library must be thread-safe "
308 "when using BatchMode.\n"));
313 void db_thread_cleanup()
317 * Return the next unique index (auto-increment) for
318 * the given table. Return NULL on error.
320 * For PostgreSQL, NULL causes the auto-increment value
323 int db_next_index(JCR *jcr, B_DB *mdb, char *table, char *index)
325 strcpy(index, "NULL");
331 * Escape strings so that PostgreSQL is happy
333 * NOTE! len is the length of the old string. Your new
334 * string must be long enough (max 2*old+1) to hold
335 * the escaped output.
338 db_escape_string(JCR *jcr, B_DB *mdb, char *snew, char *old, int len)
342 PQescapeStringConn(mdb->db, snew, old, len, &error);
344 Jmsg(jcr, M_FATAL, 0, _("PQescapeStringConn returned non-zero.\n"));
345 /* error on encoding, probably invalid multibyte encoding in the source string
346 see PQescapeStringConn documentation for details. */
347 Dmsg0(500, "PQescapeStringConn failed\n");
352 * Submit a general SQL command (cmd), and for each row returned,
353 * the sqlite_handler is called with the ctx.
355 bool db_sql_query(B_DB *mdb, const char *query, DB_RESULT_HANDLER *result_handler, void *ctx)
359 Dmsg0(500, "db_sql_query started\n");
362 if (sql_query(mdb, query) != 0) {
363 Mmsg(mdb->errmsg, _("Query failed: %s: ERR=%s\n"), query, sql_strerror(mdb));
365 Dmsg0(500, "db_sql_query failed\n");
368 Dmsg0(500, "db_sql_query succeeded. checking handler\n");
370 if (result_handler != NULL) {
371 Dmsg0(500, "db_sql_query invoking handler\n");
372 if ((mdb->result = sql_store_result(mdb)) != NULL) {
373 int num_fields = sql_num_fields(mdb);
375 Dmsg0(500, "db_sql_query sql_store_result suceeded\n");
376 while ((row = sql_fetch_row(mdb)) != NULL) {
378 Dmsg0(500, "db_sql_query sql_fetch_row worked\n");
379 if (result_handler(ctx, num_fields, row))
383 sql_free_result(mdb);
388 Dmsg0(500, "db_sql_query finished\n");
395 POSTGRESQL_ROW my_postgresql_fetch_row(B_DB *mdb)
398 POSTGRESQL_ROW row = NULL; // by default, return NULL
400 Dmsg0(500, "my_postgresql_fetch_row start\n");
402 if (!mdb->row || mdb->row_size < mdb->num_fields) {
403 int num_fields = mdb->num_fields;
404 Dmsg1(500, "we have need space of %d bytes\n", sizeof(char *) * mdb->num_fields);
407 Dmsg0(500, "my_postgresql_fetch_row freeing space\n");
410 num_fields += 20; /* add a bit extra */
411 mdb->row = (POSTGRESQL_ROW)malloc(sizeof(char *) * num_fields);
412 mdb->row_size = num_fields;
414 // now reset the row_number now that we have the space allocated
418 // if still within the result set
419 if (mdb->row_number >= 0 && mdb->row_number < mdb->num_rows) {
420 Dmsg2(500, "my_postgresql_fetch_row row number '%d' is acceptable (0..%d)\n", mdb->row_number, mdb->num_rows);
421 // get each value from this row
422 for (j = 0; j < mdb->num_fields; j++) {
423 mdb->row[j] = PQgetvalue(mdb->result, mdb->row_number, j);
424 Dmsg2(500, "my_postgresql_fetch_row field '%d' has value '%s'\n", j, mdb->row[j]);
426 // increment the row number for the next call
431 Dmsg2(500, "my_postgresql_fetch_row row number '%d' is NOT acceptable (0..%d)\n", mdb->row_number, mdb->num_rows);
434 Dmsg1(500, "my_postgresql_fetch_row finishes returning %p\n", row);
439 int my_postgresql_max_length(B_DB *mdb, int field_num) {
441 // for a given column, find the max length
448 for (i = 0; i < mdb->num_rows; i++) {
449 if (PQgetisnull(mdb->result, i, field_num)) {
450 this_length = 4; // "NULL"
452 this_length = cstrlen(PQgetvalue(mdb->result, i, field_num));
455 if (max_length < this_length) {
456 max_length = this_length;
463 POSTGRESQL_FIELD * my_postgresql_fetch_field(B_DB *mdb)
467 Dmsg0(500, "my_postgresql_fetch_field starts\n");
469 if (!mdb->fields || mdb->fields_size < mdb->num_fields) {
473 Dmsg1(500, "allocating space for %d fields\n", mdb->num_fields);
474 mdb->fields = (POSTGRESQL_FIELD *)malloc(sizeof(POSTGRESQL_FIELD) * mdb->num_fields);
475 mdb->fields_size = mdb->num_fields;
477 for (i = 0; i < mdb->num_fields; i++) {
478 Dmsg1(500, "filling field %d\n", i);
479 mdb->fields[i].name = PQfname(mdb->result, i);
480 mdb->fields[i].max_length = my_postgresql_max_length(mdb, i);
481 mdb->fields[i].type = PQftype(mdb->result, i);
482 mdb->fields[i].flags = 0;
484 Dmsg4(500, "my_postgresql_fetch_field finds field '%s' has length='%d' type='%d' and IsNull=%d\n",
485 mdb->fields[i].name, mdb->fields[i].max_length, mdb->fields[i].type,
486 mdb->fields[i].flags);
490 // increment field number for the next time around
492 Dmsg0(500, "my_postgresql_fetch_field finishes\n");
493 return &mdb->fields[mdb->field_number++];
496 void my_postgresql_data_seek(B_DB *mdb, int row)
498 // set the row number to be returned on the next call
499 // to my_postgresql_fetch_row
500 mdb->row_number = row;
503 void my_postgresql_field_seek(B_DB *mdb, int field)
505 mdb->field_number = field;
509 * Note, if this routine returns 1 (failure), Bacula expects
510 * that no result has been stored.
511 * This is where QUERY_DB comes with Postgresql.
513 * Returns: 0 on success
517 int my_postgresql_query(B_DB *mdb, const char *query)
519 Dmsg0(500, "my_postgresql_query started\n");
520 // We are starting a new query. reset everything.
522 mdb->row_number = -1;
523 mdb->field_number = -1;
526 PQclear(mdb->result); /* hmm, someone forgot to free?? */
530 Dmsg1(500, "my_postgresql_query starts with '%s'\n", query);
532 for (int i=0; i < 10; i++) {
533 mdb->result = PQexec(mdb->db, query);
540 Dmsg1(50, "Query failed: %s\n", query);
544 mdb->status = PQresultStatus(mdb->result);
545 if (mdb->status == PGRES_TUPLES_OK || mdb->status == PGRES_COMMAND_OK) {
546 Dmsg1(500, "we have a result\n", query);
548 // how many fields in the set?
549 mdb->num_fields = (int)PQnfields(mdb->result);
550 Dmsg1(500, "we have %d fields\n", mdb->num_fields);
552 mdb->num_rows = PQntuples(mdb->result);
553 Dmsg1(500, "we have %d rows\n", mdb->num_rows);
555 mdb->row_number = 0; /* we can start to fetch something */
556 mdb->status = 0; /* succeed */
558 Dmsg1(50, "Result status failed: %s\n", query);
562 Dmsg0(500, "my_postgresql_query finishing\n");
566 Dmsg1(500, "we failed\n", query);
567 PQclear(mdb->result);
569 mdb->status = 1; /* failed */
573 void my_postgresql_free_result(B_DB *mdb)
578 PQclear(mdb->result);
594 int my_postgresql_currval(B_DB *mdb, const char *table_name)
596 // Obtain the current value of the sequence that
597 // provides the serial value for primary key of the table.
599 // currval is local to our session. It is not affected by
600 // other transactions.
602 // Determine the name of the sequence.
603 // PostgreSQL automatically creates a sequence using
604 // <table>_<column>_seq.
605 // At the time of writing, all tables used this format for
606 // for their primary key: <table>id
607 // Except for basefiles which has a primary key on baseid.
608 // Therefore, we need to special case that one table.
610 // everything else can use the PostgreSQL formula.
612 char sequence[NAMEDATALEN-1];
613 char query [NAMEDATALEN+50];
617 if (strcasecmp(table_name, "basefiles") == 0) {
618 bstrncpy(sequence, "basefiles_baseid", sizeof(sequence));
620 bstrncpy(sequence, table_name, sizeof(sequence));
621 bstrncat(sequence, "_", sizeof(sequence));
622 bstrncat(sequence, table_name, sizeof(sequence));
623 bstrncat(sequence, "id", sizeof(sequence));
626 bstrncat(sequence, "_seq", sizeof(sequence));
627 bsnprintf(query, sizeof(query), "SELECT currval('%s')", sequence);
629 Dmsg1(500, "my_postgresql_currval invoked with '%s'\n", query);
630 for (int i=0; i < 10; i++) {
631 result = PQexec(mdb->db, query);
638 Dmsg1(50, "Query failed: %s\n", query);
642 Dmsg0(500, "exec done");
644 if (PQresultStatus(result) == PGRES_TUPLES_OK) {
645 Dmsg0(500, "getting value");
646 id = atoi(PQgetvalue(result, 0, 0));
647 Dmsg2(500, "got value '%s' which became %d\n", PQgetvalue(result, 0, 0), id);
649 Dmsg1(50, "Result status failed: %s\n", query);
650 Mmsg1(&mdb->errmsg, _("error fetching currval: %s\n"), PQerrorMessage(mdb->db));
659 #ifdef HAVE_BATCH_FILE_INSERT
661 int my_postgresql_batch_start(JCR *jcr, B_DB *mdb)
663 const char *query = "COPY batch FROM STDIN";
665 Dmsg0(500, "my_postgresql_batch_start started\n");
667 if (my_postgresql_query(mdb,
668 "CREATE TEMPORARY TABLE batch ("
674 "md5 varchar)") == 1)
676 Dmsg0(500, "my_postgresql_batch_start failed\n");
680 // We are starting a new query. reset everything.
682 mdb->row_number = -1;
683 mdb->field_number = -1;
685 my_postgresql_free_result(mdb);
687 for (int i=0; i < 10; i++) {
688 mdb->result = PQexec(mdb->db, query);
695 Dmsg1(50, "Query failed: %s\n", query);
699 mdb->status = PQresultStatus(mdb->result);
700 if (mdb->status == PGRES_COPY_IN) {
701 // how many fields in the set?
702 mdb->num_fields = (int) PQnfields(mdb->result);
706 Dmsg1(50, "Result status failed: %s\n", query);
710 Dmsg0(500, "my_postgresql_batch_start finishing\n");
715 Mmsg1(&mdb->errmsg, _("error starting batch mode: %s"), PQerrorMessage(mdb->db));
717 PQclear(mdb->result);
722 /* set error to something to abort operation */
723 int my_postgresql_batch_end(JCR *jcr, B_DB *mdb, const char *error)
728 Dmsg0(500, "my_postgresql_batch_end started\n");
730 if (!mdb) { /* no files ? */
735 res = PQputCopyEnd(mdb->db, error);
736 } while (res == 0 && --count > 0);
744 Dmsg0(500, "we failed\n");
746 Mmsg1(&mdb->errmsg, _("error ending batch mode: %s"), PQerrorMessage(mdb->db));
749 /* Check command status and return to normal libpq state */
750 result = PQgetResult(mdb->db);
751 if (PQresultStatus(result) != PGRES_COMMAND_OK) {
752 Mmsg1(&mdb->errmsg, _("error ending batch mode: %s"), PQerrorMessage(mdb->db));
757 Dmsg0(500, "my_postgresql_batch_end finishing\n");
762 int my_postgresql_batch_insert(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
770 mdb->esc_name = check_pool_memory_size(mdb->esc_name, mdb->fnl*2+1);
771 my_postgresql_copy_escape(mdb->esc_name, mdb->fname, mdb->fnl);
773 mdb->esc_path = check_pool_memory_size(mdb->esc_path, mdb->pnl*2+1);
774 my_postgresql_copy_escape(mdb->esc_path, mdb->path, mdb->pnl);
776 if (ar->Digest == NULL || ar->Digest[0] == 0) {
782 len = Mmsg(mdb->cmd, "%u\t%s\t%s\t%s\t%s\t%s\n",
783 ar->FileIndex, edit_int64(ar->JobId, ed1), mdb->esc_path,
784 mdb->esc_name, ar->attr, digest);
787 res = PQputCopyData(mdb->db,
790 } while (res == 0 && --count > 0);
799 Dmsg0(500, "we failed\n");
801 Mmsg1(&mdb->errmsg, _("error copying in batch mode: %s"), PQerrorMessage(mdb->db));
804 Dmsg0(500, "my_postgresql_batch_insert finishing\n");
809 #endif /* HAVE_BATCH_FILE_INSERT */
812 * Escape strings so that PostgreSQL is happy on COPY
814 * NOTE! len is the length of the old string. Your new
815 * string must be long enough (max 2*old+1) to hold
816 * the escaped output.
818 char *my_postgresql_copy_escape(char *dest, char *src, size_t len)
820 /* we have to escape \t, \n, \r, \ */
823 while (len > 0 && *src) {
858 #ifdef HAVE_BATCH_FILE_INSERT
859 const char *my_pg_batch_lock_path_query =
860 "BEGIN; LOCK TABLE Path IN SHARE ROW EXCLUSIVE MODE";
863 const char *my_pg_batch_lock_filename_query =
864 "BEGIN; LOCK TABLE Filename IN SHARE ROW EXCLUSIVE MODE";
866 const char *my_pg_batch_unlock_tables_query = "COMMIT";
868 const char *my_pg_batch_fill_path_query =
869 "INSERT INTO Path (Path) "
870 "SELECT a.Path FROM "
871 "(SELECT DISTINCT Path FROM batch) AS a "
872 "WHERE NOT EXISTS (SELECT Path FROM Path WHERE Path = a.Path) ";
875 const char *my_pg_batch_fill_filename_query =
876 "INSERT INTO Filename (Name) "
877 "SELECT a.Name FROM "
878 "(SELECT DISTINCT Name FROM batch) as a "
880 "(SELECT Name FROM Filename WHERE Name = a.Name)";
881 #endif /* HAVE_BATCH_FILE_INSERT */
883 #endif /* HAVE_POSTGRESQL */