xTuple.com xTupleU Blog & News Customer Support

Using pg_dumpall to backup/restore xTuple database

We’re running the MFG version 4.11.1.

The pg_dumpall command is very useful for backing up & restoring the complete database; however, I’m running into some odd back-end errors when I use this method. I’m not sure if it has anything to do with the PostgreSQL version difference, but according to the documentation I’ve read, it shouldn’t.

To backup the PostgreSQL 9.3 data, I use:
/opt/local/lib/postgresql93/bin/pg_dumpall -c -U postgres -f backupfile.sql

The “-c” option causes pg_dumpall to include all the necessary “cleanup” commands to drop & create everything, so you can import the file on top of an existing xTuple install. This is convenient for migrations, maintaining a “hot spare” server, etc.

To restore into PostgreSQL 9.6, I use:
/opt/local/lib/postgresql96/bin/psql -U postgres -f backupfile.sql

When I open xTuple on the restored system, and view the database log, I see the errors below.

Fri Jan 19 13:49:56 2018 Debug: "Manufacturing"
Fri Jan 19 13:49:58 2018 Warning: QLayout: Attempting to add QLayout "" to GUIClient "", which already has a layout
Fri Jan 19 13:50:01 2018 Debug: 
Starting Dashboards WebSocket Server...
Fri Jan 19 13:50:01 2018 Debug: 
xTuple Dashboards ws.Server listening on ws://127.0.0.1::3030
Fri Jan 19 13:50:02 2018 Debug: 
Starting Dashboards HTTP Server...
Fri Jan 19 13:50:02 2018 Debug: 
xTuple Dashboards http.Server listening on http://127.0.0.1::3000
Fri Jan 19 13:50:04 2018 ERROR:  function xtdash.performaction(unknown, unknown) does not exist
LINE 1: SELECT xtdash.performAction('LOAD_DASHBOARD' , '{"dashboardT...
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
(42883) QPSQL: Unable to create query
SELECT xtdash.performAction('LOAD_DASHBOARD' , '{"dashboardType":"user","objKey":"jon"}' ) AS response
Fri Jan 19 13:50:04 2018 Warning: 
Warning: a promise was rejected with a non-error: [object String]
Fri Jan 19 13:50:04 2018 ERROR:  function xtdash.performaction(unknown, unknown) does not exist
LINE 1: SELECT xtdash.performAction('LOAD_DATA_SOURCES' , NULL ) AS ...
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
(42883) QPSQL: Unable to create query
SELECT xtdash.performAction('LOAD_DATA_SOURCES' , NULL ) AS response
Fri Jan 19 13:50:04 2018 Warning: 
Warning: a promise was rejected with a non-error: [object String]
Fri Jan 19 13:50:04 2018 Debug: 
Dashboard API Query ERROR: Query failed: ERROR:  function xtdash.performaction(unknown, unknown) does not exist
LINE 1: SELECT xtdash.performAction('LOAD_DASHBOARD' , '{"dashboardT...
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
(42883) QPSQL: Unable to create query
Fri Jan 19 13:50:04 2018 Warning: 
Warning: a promise was rejected with a non-error: [object Object]
Fri Jan 19 13:50:04 2018 Debug: 
Dashboard API Query ERROR: Query failed: ERROR:  function xtdash.performaction(unknown, unknown) does not exist
LINE 1: SELECT xtdash.performAction('LOAD_DATA_SOURCES' , NULL ) AS ...
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
(42883) QPSQL: Unable to create query
Fri Jan 19 13:50:04 2018 Warning: 
Warning: a promise was rejected with a non-error: [object Object]
Fri Jan 19 13:50:04 2018 Critical: 
Dashboards API ERROR: [object Object]
Stacktrace:
<native>('Dashboards API ERROR:', [object Object]) at -1
handleActionReject(reason = [object Object]) at ws-server:81
tryCatcher([object Object]) at bluebird:5255
<anonymous>(handler = function handleActionReject(reason) {
            console.error('Dashboards API ERROR:', reason);
            // Changed performAction error propagation. Remove this old version
            // if the new one below works well.
            //socket.send(JSON.stringify({error: {message: reason}}));
            socket.send(JSON.stringify(reason));
          }, receiver = undefined, value = [object Object], promise = [object Promise]) at bluebird:3277
<anonymous>(promise = [object Promise], handler = function handleActionReject(reason) {
            console.error('Dashboards API ERROR:', reason);
            // Changed performAction error propagation. Remove this old version
            // if the new one below works well.
            //socket.send(JSON.stringify({error: {message: reason}}));
            socket.send(JSON.stringify(reason));
          }, receiver = undefined, value = [object Object]) at bluebird:3334
<anonymous>(handler = function handleActionReject(reason) {
            console.error('Dashboards API ERROR:', reason);
            // Changed performAction error propagation. Remove this old version
            // if the new one below works well.
            //socket.send(JSON.stringify({error: {message: reason}}));
            socket.send(JSON.stringify(reason));
          }, value = [object Object], bitField = 16777217) at bluebird:3379
<anonymous>() at bluebird:3454
<anonymous>(queue = [object Object]) at bluebird:187
<anonymous>() at bluebird:197
<anonymous>() at bluebird:71
<anonymous>() at timers:53
<global>() at -1
Fri Jan 19 13:50:04 2018 Critical: 
Dashboards API ERROR: [object Object]
Stacktrace:
<native>('Dashboards API ERROR:', [object Object]) at -1
handleActionReject(reason = [object Object]) at ws-server:81
tryCatcher([object Object]) at bluebird:5255
<anonymous>(handler = function handleActionReject(reason) {
            console.error('Dashboards API ERROR:', reason);
            // Changed performAction error propagation. Remove this old version
            // if the new one below works well.
            //socket.send(JSON.stringify({error: {message: reason}}));
            socket.send(JSON.stringify(reason));
          }, receiver = undefined, value = [object Object], promise = [object Promise]) at bluebird:3277
<anonymous>(promise = [object Promise], handler = function handleActionReject(reason) {
            console.error('Dashboards API ERROR:', reason);
            // Changed performAction error propagation. Remove this old version
            // if the new one below works well.
            //socket.send(JSON.stringify({error: {message: reason}}));
            socket.send(JSON.stringify(reason));
          }, receiver = undefined, value = [object Object]) at bluebird:3334
<anonymous>(handler = function handleActionReject(reason) {
            console.error('Dashboards API ERROR:', reason);
            // Changed performAction error propagation. Remove this old version
            // if the new one below works well.
            //socket.send(JSON.stringify({error: {message: reason}}));
            socket.send(JSON.stringify(reason));
          }, value = [object Object], bitField = 16777217) at bluebird:3379
<anonymous>() at bluebird:3454
<anonymous>(queue = [object Object]) at bluebird:187
<anonymous>() at bluebird:197
<anonymous>() at bluebird:71
<anonymous>() at timers:53
<global>() at -1
Fri Jan 19 13:50:12 2018 Debug: Looking for scripts ("QObject", "QWidget", "XWidget", "errorLog")

I’ve tried running Gil’s script (Upgrade 4.4.0->4.9.2, ERROR: function "xt.js_init" does not exist), but that throws an error (plv8 is already installed):

WARNING: failed to find js function javascript function is not found for “xt.js_init”.

Is there some sort of initialization SQL script that I need to run in order to make this work properly?

This is not a xt.js_init problem. Does the function performaction exist in the xtdash schema? If it does, then it is a type error with this functions input parameters. I note you are backing up from a 9.3 to a 9.6 database. This shouldn’t be a problem but I have seen times where things like this are occurring in other PostgreSQL databases.

Can you check you have the latest version of the xtdash extension package loaded. That might have resolved this issue. I’m fairly sure other companies have Dashboards running on 9.6 databases.

We have the latest xtdash installed.

What’s odd is that, if I run “show plv8.start_proc;” it returns “xt.js_init” as it should…so plv8 is installed; however, if I comment out the plv8 load line (plv8.start_proc = ‘xt.js_init’) in postgresql.conf, I get the identical errors in the database log.

So it’s as if the database “thinks” that plv8 is installed, but it really isn’t.

It has nothing to do with the js_init. That is not the issue here. The problem is the call to the xtdash.performaction() function is happening and the database is not recognising the input parameter types and as such cant “find” the function. The simplest fix is to find the process that is calling the performaction() function and specify the correct input types.

Raise an issue with lots of detail and someone should be able to find a solution fairly quickly.

The issue (as I see it) is that I’m doing a “pg_dumpall” of a completely functional xTuple RDBMS, creating an exact duplicate of that RDBMS (the only difference being PostgreSQL 9.6 vs 9.3), and the duplicate is causing the same desktop client to throw these errors. It has to be something simple, because I’m not changing anything at all about the “guts” of the database. I think solving this issue is of interest to the community, because it (should be) a valid technique for use in a daily backup or BC/DR plan.

Is there something that a “pg_dumpall -c” doesn’t catch?

Given identical backend databases on two servers, is there something about a difference in the server hostname that affects the desktop client (other than having to change the hostname in the connection dialog)?

To eliminate some variables, I did the following:

  1. On the backup server, wiped out the entire PostgreSQL 9.6 database instance, and initialized a new database. Reconfigured postgresql.conf to include the necessary plv8 init line, and pg_hba.conf for network access etc.

  2. On the production server (xT MFG 4.11.1 on PostgreSQL 9.3), used “pg_dumpall -c” with the “-l” flag to only dump the xTuple databases (i.e. didn’t export the postgres table space etc.).

  3. On the backup server, imported the dump using “psql -U postgres -f xtdb.sql”.

  4. On the backup server, applied the 4.11.2 MFG update. The updater app complained about PostgreSQL 9.6, but the update ran successfully.

  5. Connected to the backup server using the 4.11.2 desktop client.

I get the same exact errors as above.

I’m actually not sure what you’re asking here. This is basically just stock xTuple MFG, we haven’t made any modifications to the xtdash configuration at all. I don’t see the “performaction” function in the xtdash schema. It’s there in the 4.11.1/pgsql 9.3 production server, but somehow it’s not making it over to the backup. In fact, neither is the “json_keypair” function. Both functions are created in the exported SQL (created with “pg_dumpall -c”)…however, I do notice that those functions (and several other places) have DOS linefeeds (^M) in the dump file, and sometimes those can cause errors. So, I’ll have to try a different technique than pg_dumpall to see if maybe that’s the issue.

It appears to be a plv8 issue. My guess is that the plv8 extension is not backed up and/or restored in such a way that it is available during the operation.

Backing up with:
pg_dumpall -c -U postgres --quote-all-identifiers --disable-triggers -f backup.sql

and restoring with:
psql -U postgres -f backup.sql

Results in a bunch of errors like these:

ERROR:  language "plv8" does not exist
ERROR:  function xt.install_js(text, text, text, text, boolean) does not exist
ERROR:  language "plv8" does not exist
ERROR:  function xt.js_init(boolean, boolean) does not exist
ERROR:  language "plv8" does not exist
ERROR:  function xt.parseediprofile(text, text) does not exist
ERROR:  language "plv8" does not exist
ERROR:  function xt.parsemetasql(text, text) does not exist

…and a bunch of missing functions in the xtdash schema. I presume that any function that uses plv8, simply doesn’t get restored. This is a significant problem.

I edited the “backup.sql” file (from the pg_dumpall command above) and added Gil’s script after every “\connect” command (which is where the SQL connects to a fresh database). This resulted in (seemingly) all of the functions being restored properly. Didn’t see any function-related errors in the import log, at any rate.

Now, when I open xTuple desktop, I see these errors in the log:

Wed Jan 24 19:21:17 2018 Debug: "Manufacturing"
Wed Jan 24 19:21:19 2018 Warning: QLayout: Attempting to add QLayout "" to GUIClient "", which already has a layout
Wed Jan 24 19:21:20 2018 Debug: 
Starting Dashboards WebSocket Server...
Wed Jan 24 19:21:20 2018 Debug: 
xTuple Dashboards ws.Server listening on ws://127.0.0.1::3030
Wed Jan 24 19:21:21 2018 Debug: 
Starting Dashboards HTTP Server...
Wed Jan 24 19:21:21 2018 Debug: 
xTuple Dashboards http.Server listening on http://127.0.0.1::3000
Wed Jan 24 19:21:23 2018 ERROR:  ReferenceError: XT is not defined
DETAIL:  performaction() LINE 13:   XT.username = plv8.execute("SELECT CURRENT_USER AS user;")[0].user;
(XX000) QPSQL: Unable to create query
SELECT xtdash.performAction('LOAD_DASHBOARD' , '{"dashboardType":"user","objKey":"jon"}' ) AS response
Wed Jan 24 19:21:23 2018 Warning: 
Warning: a promise was rejected with a non-error: [object String]
Wed Jan 24 19:21:23 2018 ERROR:  ReferenceError: XT is not defined
DETAIL:  performaction() LINE 13:   XT.username = plv8.execute("SELECT CURRENT_USER AS user;")[0].user;
(XX000) QPSQL: Unable to create query
SELECT xtdash.performAction('LOAD_DATA_SOURCES' , NULL ) AS response
Wed Jan 24 19:21:23 2018 Warning: 
Warning: a promise was rejected with a non-error: [object String]
Wed Jan 24 19:21:23 2018 Debug: 
Dashboard API Query ERROR: Query failed: ERROR:  ReferenceError: XT is not defined
DETAIL:  performaction() LINE 13:   XT.username = plv8.execute("SELECT CURRENT_USER AS user;")[0].user;
(XX000) QPSQL: Unable to create query
Wed Jan 24 19:21:23 2018 Warning: 
Warning: a promise was rejected with a non-error: [object Object]
Wed Jan 24 19:21:23 2018 Debug: 
Dashboard API Query ERROR: Query failed: ERROR:  ReferenceError: XT is not defined
DETAIL:  performaction() LINE 13:   XT.username = plv8.execute("SELECT CURRENT_USER AS user;")[0].user;
(XX000) QPSQL: Unable to create query
Wed Jan 24 19:21:23 2018 Warning: 
Warning: a promise was rejected with a non-error: [object Object]
Wed Jan 24 19:21:23 2018 Critical: 
Dashboards API ERROR: [object Object]
Stacktrace:
<native>('Dashboards API ERROR:', [object Object]) at -1
handleActionReject(reason = [object Object]) at ws-server:81
tryCatcher([object Object]) at bluebird:5255
<anonymous>(handler = function handleActionReject(reason) {
        console.error('Dashboards API ERROR:', reason);
        // Changed performAction error propagation. Remove this old version
        // if the new one below works well.
        //socket.send(JSON.stringify({error: {message: reason}}));
        socket.send(JSON.stringify(reason));
      }, receiver = undefined, value = [object Object], promise = [object Promise]) at bluebird:3277
<anonymous>(promise = [object Promise], handler = function handleActionReject(reason) {
        console.error('Dashboards API ERROR:', reason);
        // Changed performAction error propagation. Remove this old version
        // if the new one below works well.
        //socket.send(JSON.stringify({error: {message: reason}}));
        socket.send(JSON.stringify(reason));
      }, receiver = undefined, value = [object Object]) at bluebird:3334
<anonymous>(handler = function handleActionReject(reason) {
        console.error('Dashboards API ERROR:', reason);
        // Changed performAction error propagation. Remove this old version
        // if the new one below works well.
        //socket.send(JSON.stringify({error: {message: reason}}));
        socket.send(JSON.stringify(reason));
      }, value = [object Object], bitField = 16777217) at bluebird:3379
<anonymous>() at bluebird:3454
<anonymous>(queue = [object Object]) at bluebird:187
<anonymous>() at bluebird:197
<anonymous>() at bluebird:71
<anonymous>() at timers:53
<global>() at -1
Wed Jan 24 19:21:23 2018 Critical: 
Dashboards API ERROR: [object Object]
Stacktrace:
<native>('Dashboards API ERROR:', [object Object]) at -1
handleActionReject(reason = [object Object]) at ws-server:81
tryCatcher([object Object]) at bluebird:5255
<anonymous>(handler = function handleActionReject(reason) {
        console.error('Dashboards API ERROR:', reason);
        // Changed performAction error propagation. Remove this old version
        // if the new one below works well.
        //socket.send(JSON.stringify({error: {message: reason}}));
        socket.send(JSON.stringify(reason));
      }, receiver = undefined, value = [object Object], promise = [object Promise]) at bluebird:3277
<anonymous>(promise = [object Promise], handler = function handleActionReject(reason) {
        console.error('Dashboards API ERROR:', reason);
        // Changed performAction error propagation. Remove this old version
        // if the new one below works well.
        //socket.send(JSON.stringify({error: {message: reason}}));
        socket.send(JSON.stringify(reason));
      }, receiver = undefined, value = [object Object]) at bluebird:3334
<anonymous>(handler = function handleActionReject(reason) {
        console.error('Dashboards API ERROR:', reason);
        // Changed performAction error propagation. Remove this old version
        // if the new one below works well.
        //socket.send(JSON.stringify({error: {message: reason}}));
        socket.send(JSON.stringify(reason));
      }, value = [object Object], bitField = 16777217) at bluebird:3379
<anonymous>() at bluebird:3454
<anonymous>(queue = [object Object]) at bluebird:187
<anonymous>() at bluebird:197
<anonymous>() at bluebird:71
<anonymous>() at timers:53
<global>() at -1
Wed Jan 24 19:21:26 2018 Debug: Looking for scripts ("QObject", "QWidget", "XWidget", "errorLog")

Which looks less bad than before. I then apply the 4.11.2 update, connect using the 4.11.2 client, and the log is downright friendly:

Wed Jan 24 19:24:57 2018 Debug: "en_us"
Wed Jan 24 19:25:08 2018 Debug: "Manufacturing"
Wed Jan 24 19:25:10 2018 Warning: QLayout: Attempting to add QLayout "" to GUIClient "", which already has a layout
Wed Jan 24 19:25:11 2018 Debug: 
Starting Dashboards WebSocket Server...
Wed Jan 24 19:25:11 2018 Debug: 
xTuple Dashboards ws.Server listening on ws://127.0.0.1::3030
Wed Jan 24 19:25:12 2018 Debug: 
Starting Dashboards HTTP Server...
Wed Jan 24 19:25:12 2018 Debug: 
xTuple Dashboards http.Server listening on http://127.0.0.1::3000
Wed Jan 24 19:25:29 2018 Debug: Looking for scripts ("QObject", "QWidget", "XWidget", "errorLog")

So, apparently it’s fixed. Obviously some of the issues were related to changing the postgresql version (9.3->9.6) and updating xTuple from 4.11.1->4.11.2, but the main issue was with pg_dumpall and plv8.

In summary: You can use pg_dumpall to backup an xTuple database, but you’ll have to manually edit the resulting SQL file and add Gil’s script after every “\connect” action (there will be one for every table in your postgres installation) in order to get a viable import. You have to add the script because, for some reason, the commands necessary to enable plv8 are not included in any backup file.

How to backup & restore an entire xTuple database using pg_dumpall:

  1. Create the backup file:

pg_dumpall -c -U postgres --quote-all-identifiers --disable-triggers -f backup.sql

  1. Edit the backup file, and insert Gil’s script (linked above) after every “\connect” line.

  2. Restore the backup file (ostensibly on a BC/DR hot-spare, though you could also use this when upgrading postgresql versions):

psql -U postgres -f backup.sql

I may be the only one who cares, but for completeness’ sake, here are the scripts. Other *nixen may need to change the import script’s “sed -e” to a “sed -i” in order to work properly. Note that the import sequence completely overwrites the entire PostgreSQL instance. It can be used to create a duplicate of a PostgreSQL instance on a fresh install of PostgreSQL–useful for maintaining an offsite backup, hot spare, etc.

For the curious…I used sed instead of awk, perl, or (insert your favorite stream editor here) because it was easier for me. I wanted to include Gil’s script in the shell script itself, but with sed it was just easier to put it in its own file. Performance is acceptable; the sed process takes much less time than the actual psql import. Feel free to come up with your own way to skin this cat.

Export script:

#!/bin/bash
BF=/full/path/to/your_backup_file.sql
/full/path/to/your/postgresql/bin/pg_dumpall -c -U postgres --quote-all-identifiers --disable-triggers -f $BF

Import script:

#!/bin/bash
BF=/full/path/to/your_backup_file.sql
PLV8=/full/path/to/gils_script.sql
sed -e '/\\connect /r $PLV8' $BF > /tmp/$BF
chown postgres /tmp/$BF
rm $BF
cd /opt/local
/full/path/to/your/postgresql/bin/psql -U postgres -f /tmp/$BF
rm /tmp/$BF

Content of /full/path/to/gils_script.sql:

do $$
declare
  count integer;
  query text;
begin
  perform *
  from information_schema.schemata
  where schema_name = 'xt';
  get diagnostics count = row_count;
  if (count > 0) then
    return;
  end if;
  query = 'create schema xt;';
  execute query;
  query = 'grant all on schema xt to group xtrole;';
  execute query;
end;
$$ language 'plpgsql';
CREATE OR REPLACE FUNCTION xt.js_init(debug BOOLEAN DEFAULT false, initialize BOOLEAN DEFAULT false)
RETURNS VOID AS $$ BEGIN RETURN; END; $$ LANGUAGE plpgsql;
CREATE EXTENSION IF NOT EXISTS plv8;

Jon…although this is an old post…it’s a good one. I have fought thru this process using some painful techniques. I’m not comfortable with the current backup and recovery scenarios as they apply to PLV8 related functionality. I typically get things working, but dashboards end up way down on the list.

I’m posting as a reminder to all of us that this needs to be fixed.

Scott

Scott and Jon,

This should be fixed by using a newer version of plv8. I just tested with PostgreSQL 9.6.2 and an old version of plv8 — lots of errors restoring. After updating plv8 with the latest, that same .backup restored with one warning, no errors, and what appears to be a complete database, including 17 plv8 functions across 3 schemas.

Gil

I’m using PostgreSQL 9.6.3, PLV8 2.0.3 on a Mac. Here’s what I found while playing around today. I did a pgdump of a single database to a plaintext .sql file.

Create a new database, then use the following for my restore:
\i /Users/szuke/backup.sql

This fails to restore everything.

This is what I currently have to do for everything to work.

  1. Create a new database
  2. Run the Gil Script over the database, confirming that it creates the xt schema, the PLV8 extension and PLV8 language
  3. Modify the .sql backup and comment out the plv8 drop, --DROP EXTENSION “plv8”;

If I don’t do the above steps, the PLV8 extension and language do not get restored. If there is an option that I’m not doing correctly in my backup, please let me know. If I do a pgdump to a .backup file, I do not get plv8.

pg_dump -c -U admin --quote-all-identifiers --disable-triggers ref4113 -f backup.sql

NOTE: I have tried this against existing xtuplecloud databases but didn’t want to put this information in the forum.

Thanks
Scott

Hi Scott,

The fix for restoring databases came with plv8 2.3.2, please try your steps again using the latest plv8 from our wiki page. 2.3.2 was also the first version where we had version parity between windows/mac/linux, and the links from our Installing plv8 wiki page were updated sometime late April with the fixed version.

David

Thanks for the update on plv8 2.3.2. That made the difference.

An additional detail that messed me up was that when I installed 2.3.2, it didn’t actually replace the existing extensions. I have multiple databases that have 2.0.3, but my postgres database and any new databases are 2.3.2.

Is there a documented process for this update? If I try to drop the plv8 extension, I run into lots of dependency issues.

I’d be happy to help document this process if needed.

The only process really is to restart PostgreSQL when you update plv8, since the library only gets loaded when PG starts, not during a reload.

It can be confusing, but PostgreSQL extensions don’t do the best job of updating the version number in the PG catalog, as they are responsible for doing so themselves, so you can be using a version that is different than what PostgreSQL reports when you query the installed extensions. The library only exists once on disk, all databases with it “installed” use the same library.

You can run this query to find out the actual plv8 version in use:

DO $ plv8.elog(NOTICE, plv8.version); $ LANGUAGE plv8;

Which works on all plv8 versions. Newer ones have:

SELECT plv8_version();

That matches what I was seeing in the libraries, but I was confused as to why I was still seeing 2.0.3. I did a backup, drop, restore and 2.3.2 was in place.

Thanks for the help.

UPDATE: I can confirm that this issue seems to be fixed in the latest version of plv8. Once plv8 is updated (on all servers), the edit-the-backup-file step from my Jan 24 post can be eliminated, and the built-in PostgreSQL backup/restore tools used as mentioned.