{"id":31553,"date":"2024-03-04T11:15:33","date_gmt":"2024-03-04T10:15:33","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/?p=31553"},"modified":"2024-03-07T10:53:47","modified_gmt":"2024-03-07T09:53:47","slug":"getting-started-with-greenplum-4-backup-restore-databases","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/getting-started-with-greenplum-4-backup-restore-databases\/","title":{"rendered":"Getting started with Greenplum \u2013 4 \u2013 Backup &amp; Restore &#8211; databases"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">This is the fourth part of the Greenplum blog series, the previous ones are here: <a href=\"https:\/\/www.dbi-services.com\/blog\/getting-started-with-greenplum-1-installation\/\" target=\"_blank\" rel=\"noreferrer noopener\">Getting started with Greenplum \u2013 1 \u2013 Installation<\/a>, <a href=\"https:\/\/www.dbi-services.com\/blog\/getting-started-with-greenplum-2-initializing-and-bringing-up-the-cluster\/\" target=\"_blank\" rel=\"noreferrer noopener\">Getting started with Greenplum \u2013 2 \u2013 Initializing and bringing up the cluster<\/a>, <a href=\"https:\/\/www.dbi-services.com\/blog\/getting-started-with-greenplum-3-behind-the-scenes\/\" target=\"_blank\" rel=\"noreferrer noopener\">Getting started with Greenplum \u2013 3 \u2013 Behind the scenes<\/a>. In this blog we&#8217;ll look at how you are supposed to backup and restore a Greenplum cluster. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">If you restarted the cluster nodes and log on to the systems again, you&#8217;ll notice that the instances are not running:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: plain; highlight: [1,3]; title: ; notranslate\" title=\"\">\n&#x5B;gpadmin@cdw ~]$ ps -ef | grep postgres\ngpadmin     1285    1233  0 09:43 pts\/0    00:00:00 grep --color=auto potsgres\n&#x5B;gpadmin@cdw ~]$ psql postgres\npsql: error: could not connect to server: No such file or directory\n        Is the server running locally and accepting\n        connections on Unix domain socket &quot;\/tmp\/.s.PGSQL.5432&quot;?\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\">Starting and stopping the cluster is done with &#8220;gpstart&#8221; and &#8220;gpstop&#8221;, so starting it up is just a matter of this:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: bash; highlight: [1,3,32,33]; title: ; notranslate\" title=\"\">\n&#x5B;gpadmin@cdw ~]$ which gpstart\n\/usr\/local\/greenplum-db-7.1.0\/bin\/gpstart\n&#x5B;gpadmin@cdw ~]$ gpstart\n20240301:09:45:37:001290 gpstart:cdw:gpadmin-&#x5B;INFO]:-Starting gpstart with args: \n20240301:09:45:37:001290 gpstart:cdw:gpadmin-&#x5B;INFO]:-Gathering information and validating the environment...\n20240301:09:45:37:001290 gpstart:cdw:gpadmin-&#x5B;INFO]:-Greenplum Binary Version: &#039;postgres (Greenplum Database) 7.1.0 build commit:e7c2b1f14bb42a1018ac57d14f4436880e0a0515 Open Source&#039;\n20240301:09:45:37:001290 gpstart:cdw:gpadmin-&#x5B;INFO]:-Greenplum Catalog Version: &#039;302307241&#039;\n20240301:09:45:37:001290 gpstart:cdw:gpadmin-&#x5B;INFO]:-Starting Coordinator instance in admin mode\n20240301:09:45:37:001290 gpstart:cdw:gpadmin-&#x5B;INFO]:-CoordinatorStart pg_ctl cmd is env GPSESSID=0000000000 GPERA=None $GPHOME\/bin\/pg_ctl -D \/data\/coordinator\/gpseg-1\/ -l \/data\/coordinator\/gpseg-1\/\/log\/startup.log -w -t 600 -o &quot; -c gp_role=utility &quot; start\n20240301:09:45:37:001290 gpstart:cdw:gpadmin-&#x5B;INFO]:-Obtaining Greenplum Coordinator catalog information\n20240301:09:45:37:001290 gpstart:cdw:gpadmin-&#x5B;INFO]:-Obtaining Segment details from coordinator...\n20240301:09:45:37:001290 gpstart:cdw:gpadmin-&#x5B;INFO]:-Setting new coordinator era\n20240301:09:45:37:001290 gpstart:cdw:gpadmin-&#x5B;INFO]:-Coordinator Started...\n20240301:09:45:38:001290 gpstart:cdw:gpadmin-&#x5B;INFO]:-Shutting down coordinator\n20240301:09:45:38:001290 gpstart:cdw:gpadmin-&#x5B;INFO]:---------------------------\n20240301:09:45:38:001290 gpstart:cdw:gpadmin-&#x5B;INFO]:-Coordinator instance parameters\n20240301:09:45:38:001290 gpstart:cdw:gpadmin-&#x5B;INFO]:---------------------------\n20240301:09:45:38:001290 gpstart:cdw:gpadmin-&#x5B;INFO]:-Database                 = template1\n20240301:09:45:38:001290 gpstart:cdw:gpadmin-&#x5B;INFO]:-Coordinator Port              = 5432\n20240301:09:45:38:001290 gpstart:cdw:gpadmin-&#x5B;INFO]:-Coordinator directory         = \/data\/coordinator\/gpseg-1\/\n20240301:09:45:38:001290 gpstart:cdw:gpadmin-&#x5B;INFO]:-Timeout                  = 600 seconds\n20240301:09:45:38:001290 gpstart:cdw:gpadmin-&#x5B;INFO]:-Coordinator standby           = Off \n20240301:09:45:38:001290 gpstart:cdw:gpadmin-&#x5B;INFO]:---------------------------------------\n20240301:09:45:38:001290 gpstart:cdw:gpadmin-&#x5B;INFO]:-Segment instances that will be started\n20240301:09:45:38:001290 gpstart:cdw:gpadmin-&#x5B;INFO]:---------------------------------------\n20240301:09:45:38:001290 gpstart:cdw:gpadmin-&#x5B;INFO]:-   Host   Datadir                Port   Role\n20240301:09:45:38:001290 gpstart:cdw:gpadmin-&#x5B;INFO]:-   sdw1   \/data\/primary\/gpseg0   6000   Primary\n20240301:09:45:38:001290 gpstart:cdw:gpadmin-&#x5B;INFO]:-   sdw2   \/data\/mirror\/gpseg0    7000   Mirror\n20240301:09:45:38:001290 gpstart:cdw:gpadmin-&#x5B;INFO]:-   sdw2   \/data\/primary\/gpseg1   6000   Primary\n20240301:09:45:38:001290 gpstart:cdw:gpadmin-&#x5B;INFO]:-   sdw1   \/data\/mirror\/gpseg1    7000   Mirror\n\nContinue with Greenplum instance startup Yy|Nn (default=N):\n&gt; Y\n20240301:09:45:49:001290 gpstart:cdw:gpadmin-&#x5B;INFO]:-Commencing parallel primary and mirror segment instance startup, please wait...\n.\n20240301:09:45:50:001290 gpstart:cdw:gpadmin-&#x5B;INFO]:-Process results...\n20240301:09:45:50:001290 gpstart:cdw:gpadmin-&#x5B;INFO]:-----------------------------------------------------\n20240301:09:45:50:001290 gpstart:cdw:gpadmin-&#x5B;INFO]:-   Successful segment starts                                            = 4\n20240301:09:45:50:001290 gpstart:cdw:gpadmin-&#x5B;INFO]:-   Failed segment starts                                                = 0\n20240301:09:45:50:001290 gpstart:cdw:gpadmin-&#x5B;INFO]:-   Skipped segment starts (segments are marked down in configuration)   = 0\n20240301:09:45:50:001290 gpstart:cdw:gpadmin-&#x5B;INFO]:-----------------------------------------------------\n20240301:09:45:50:001290 gpstart:cdw:gpadmin-&#x5B;INFO]:-Successfully started 4 of 4 segment instances \n20240301:09:45:50:001290 gpstart:cdw:gpadmin-&#x5B;INFO]:-----------------------------------------------------\n20240301:09:45:50:001290 gpstart:cdw:gpadmin-&#x5B;INFO]:-Starting Coordinator instance cdw directory \/data\/coordinator\/gpseg-1\/ \n20240301:09:45:50:001290 gpstart:cdw:gpadmin-&#x5B;INFO]:-CoordinatorStart pg_ctl cmd is env GPSESSID=0000000000 GPERA=5519b53b4b2c1dab_240301094537 $GPHOME\/bin\/pg_ctl -D \/data\/coordinator\/gpseg-1\/ -l \/data\/coordinator\/gpseg-1\/\/log\/startup.log -w -t 600 -o &quot; -c gp_role=dispatch &quot; start\n20240301:09:45:50:001290 gpstart:cdw:gpadmin-&#x5B;INFO]:-Command pg_ctl reports Coordinator cdw instance active\n20240301:09:45:50:001290 gpstart:cdw:gpadmin-&#x5B;INFO]:-Connecting to db template1 on host localhost\n20240301:09:45:50:001290 gpstart:cdw:gpadmin-&#x5B;INFO]:-No standby coordinator configured.  skipping...\n20240301:09:45:50:001290 gpstart:cdw:gpadmin-&#x5B;INFO]:-Database successfully started\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\">Before we look at how you can recover from failed coordinator or segment nodes we&#8217;ll look at how you are supposed to backup the database(s). The tool for backup databases in Greenplum is called &#8220;<a href=\"https:\/\/docs.vmware.com\/en\/VMware-Greenplum-Backup-and-Restore\/1.30\/greenplum-backup-and-restore\/admin_guide-managing-backup-ddboost-plugin.html\" target=\"_blank\" rel=\"noreferrer noopener\">gpbackup<\/a>&#8220;. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">We&#8217;ll use the database we&#8217;ve created in the previous post to create a table containing some sample data. In a Greenplum system tables are distributed across the segments and when you create a table you have three options on how you want this to happen:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>DISTRIBUTED BY: You choose the column which will be used to distribute the data<\/li>\n\n\n\n<li>DISTRIBUTED RANDOMLY: Use this if there is no unique column<\/li>\n\n\n\n<li>DISTRIBUTED REPLICATED: Every row is distributed to all segments<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">As the system works best (performance wise) when you have the same amount of data on all the segment nodes we&#8217;ll go with the first method and create the table like this:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; highlight: [1,5,6,7]; title: ; notranslate\" title=\"\">\n&#x5B;gpadmin@cdw ~]$ psql d\npsql (12.12)\nType &quot;help&quot; for help.\n\nd=# create table t1 ( id int primary key\n                    , dummy text \n                    ) distributed by (id);\nCREATE TABLE\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\">For populating the table we will use the standard <a href=\"https:\/\/www.postgresql.org\/docs\/current\/functions-srf.html\" target=\"_blank\" rel=\"noreferrer noopener\">generate_series<\/a> PostgreSQL function:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; highlight: [1,2,3]; title: ; notranslate\" title=\"\">\nd=# insert into t1 \n    select i, md5(i::text) \n      from generate_series(1,1000000) i;\nINSERT 0 1000000\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\">When we try to backup this database with &#8220;gpbackup&#8221;, there is our first surprise. This utility is not available by default in the open source version of Greenplum:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: bash; highlight: [1,3]; title: ; notranslate\" title=\"\">\n&#x5B;gpadmin@cdw ~]$ which gpbackup\n\/usr\/bin\/which: no gpbackup in (\/usr\/local\/greenplum-db-7.1.0\/bin:\/home\/gpadmin\/.local\/bin:\/home\/gpadmin\/bin:\/usr\/local\/bin:\/usr\/bin:\/usr\/local\/sbin:\/usr\/sbin)\n&#x5B;gpadmin@cdw ~]$ find \/usr\/local\/ -name gpbackup \n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\">We could still use the standard <a href=\"https:\/\/www.postgresql.org\/docs\/current\/app-pgdump.html\">pg_dump<\/a> utility from PostgreSQL:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: plain; highlight: [1,2]; title: ; notranslate\" title=\"\">\n&#x5B;gpadmin@cdw ~]$ pg_dump d &amp;gt; d.sql\n&#x5B;gpadmin@cdw ~]$ tail -10 d.sql \n--\n\nALTER TABLE ONLY public.t1\n    ADD CONSTRAINT t1_pkey PRIMARY KEY (id);\n\n\n--\n-- Greenplum Database database dump complete\n--\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\">In the same way you can use <a href=\"https:\/\/www.postgresql.org\/docs\/current\/app-pg-dumpall.html\" target=\"_blank\" rel=\"noreferrer noopener\">pg_dumpall<\/a> to get the global objects:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: bash; highlight: [1,2]; title: ; notranslate\" title=\"\">\n&#x5B;gpadmin@cdw ~]$ pg_dumpall --globals-only &gt; globals.sql\n&#x5B;gpadmin@cdw ~]$ cat globals.sql \n--\n-- Greenplum Database cluster dump\n--\n\nSET default_transaction_read_only = off;\n\nSET client_encoding = &#039;UTF8&#039;;\nSET standard_conforming_strings = on;\n\n--\n-- Roles\n--\n\nCREATE ROLE gpadmin;\nALTER ROLE gpadmin WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION BYPASSRLS PASSWORD &#039;md5b44a9b06d576a0b083cd60e5f875cf48&#039;;\n\n--\n-- PostgreSQL database cluster dump complete\n--\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\">As this is standard PostgreSQL stuff we&#8217;ll not look into that any further. Re-loading can either be done with &#8220;<a href=\"https:\/\/www.postgresql.org\/docs\/current\/app-psql.html\" target=\"_blank\" rel=\"noreferrer noopener\">psql<\/a>&#8221; or &#8220;<a href=\"https:\/\/www.postgresql.org\/docs\/current\/app-pgrestore.html\" target=\"_blank\" rel=\"noreferrer noopener\">pg_restore<\/a>&#8221; as usual.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">For getting the <a href=\"https:\/\/docs.vmware.com\/en\/VMware-Greenplum-Backup-and-Restore\/1.30\/greenplum-backup-and-restore\/utility_guide-ref-gpbackup.html\" target=\"_blank\" rel=\"noreferrer noopener\">gpbackup<\/a> onto the system we need Go. The default version of Go which comes with Rocky Linux 9 is fine, so we can install it with dnf and fetch the latest release of gpbackup afterwards:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: bash; highlight: [1,2,4,5,6]; title: ; notranslate\" title=\"\">\n&#x5B;gpadmin@cdw ~]$ sudo dnf install golang -y\n&#x5B;gpadmin@cdw ~]$ go version\ngo version go1.20.10 linux\/amd64\n&#x5B;gpadmin@cdw ~]$ wget https:\/\/github.com\/greenplum-db\/gpbackup\/releases\/download\/1.30.3\/gpbackup_binaries_rhel9.tar.gz\n&#x5B;gpadmin@cdw ~]$ tar axf gpbackup_binaries_rhel9.tar.gz\n&#x5B;gpadmin@cdw ~]$ .\/gpbackup --version\ngpbackup version 1.30.3\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\">In the most simple form a backup can be taken like this:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: bash; highlight: [1,2,4]; title: ; notranslate\" title=\"\">\n&#x5B;gpadmin@cdw ~]$ mkdir backup\n&#x5B;gpadmin@cdw ~]$ .\/gpbackup --dbname d --backup-dir backup\/\n20240301:11:01:33 gpbackup:gpadmin:cdw:004933-&#x5B;CRITICAL]:-backup\/ is not an absolute path.\n&#x5B;gpadmin@cdw ~]$ .\/gpbackup --dbname d --backup-dir ~\/backup\/\n20240301:11:01:43 gpbackup:gpadmin:cdw:004942-&#x5B;INFO]:-gpbackup version = 1.30.3\n20240301:11:01:43 gpbackup:gpadmin:cdw:004942-&#x5B;INFO]:-Greenplum Database Version = 7.1.0 build commit:e7c2b1f14bb42a1018ac57d14f4436880e0a0515 Open Source\n20240301:11:01:43 gpbackup:gpadmin:cdw:004942-&#x5B;INFO]:-Starting backup of database d\n20240301:11:01:43 gpbackup:gpadmin:cdw:004942-&#x5B;INFO]:-Backup Timestamp = 20240301110143\n20240301:11:01:43 gpbackup:gpadmin:cdw:004942-&#x5B;INFO]:-Backup Database = d\n20240301:11:01:43 gpbackup:gpadmin:cdw:004942-&#x5B;INFO]:-Gathering table state information\n20240301:11:01:43 gpbackup:gpadmin:cdw:004942-&#x5B;INFO]:-Acquiring ACCESS SHARE locks on tables\nLocks acquired:  1 \/ 1 &#x5B;================================================================] 100.00% 0s\n20240301:11:01:43 gpbackup:gpadmin:cdw:004942-&#x5B;INFO]:-Gathering additional table metadata\n20240301:11:01:43 gpbackup:gpadmin:cdw:004942-&#x5B;INFO]:-Getting storage information\n20240301:11:01:43 gpbackup:gpadmin:cdw:004942-&#x5B;INFO]:-Metadata will be written to \/home\/gpadmin\/backup\/gpseg-1\/backups\/20240301\/20240301110143\/gpbackup_20240301110143_metadata.sql\n20240301:11:01:43 gpbackup:gpadmin:cdw:004942-&#x5B;INFO]:-Writing global database metadata\n20240301:11:01:43 gpbackup:gpadmin:cdw:004942-&#x5B;INFO]:-Global database metadata backup complete\n20240301:11:01:43 gpbackup:gpadmin:cdw:004942-&#x5B;INFO]:-Writing pre-data metadata\n20240301:11:01:43 gpbackup:gpadmin:cdw:004942-&#x5B;INFO]:-Pre-data metadata metadata backup complete\n20240301:11:01:43 gpbackup:gpadmin:cdw:004942-&#x5B;INFO]:-Writing post-data metadata\n20240301:11:01:43 gpbackup:gpadmin:cdw:004942-&#x5B;INFO]:-Post-data metadata backup complete\n20240301:11:01:43 gpbackup:gpadmin:cdw:004942-&#x5B;INFO]:-Writing data to file\nTables backed up:  1 \/ 1 &#x5B;=================================================================] 100.00%\n&#x5B;-----------------------------------------------------------------------------------------=]   0.00%\n20240301:11:01:44 gpbackup:gpadmin:cdw:004942-&#x5B;INFO]:-Data backup complete\n20240301:11:01:45 gpbackup:gpadmin:cdw:004942-&#x5B;INFO]:-Found neither \/usr\/local\/greenplum-db-7.1.0\/bin\/gp_email_contacts.yaml nor \/home\/gpadmin\/gp_email_contacts.yaml\n20240301:11:01:45 gpbackup:gpadmin:cdw:004942-&#x5B;INFO]:-Email containing gpbackup report \/home\/gpadmin\/backup\/gpseg-1\/backups\/20240301\/20240301110143\/gpbackup_20240301110143_report will not be sent\n20240301:11:01:45 gpbackup:gpadmin:cdw:004942-&#x5B;INFO]:-Backup completed successfully\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\">The global stuff went into &#8220;\/home\/gpadmin\/backup\/gpseg-1\/backups\/20240301\/20240301110143\/gpbackup_20240301110143_metadata.sql&#8221;:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: bash; highlight: [1]; title: ; notranslate\" title=\"\">\n&#x5B;gpadmin@cdw ~]$ cat \/home\/gpadmin\/backup\/gpseg-1\/backups\/20240301\/20240301110143\/gpbackup_20240301110143_metadata.sql | egrep -v &quot;^$|^#&quot;\nSET client_encoding = &#039;UTF8&#039;;\nALTER RESOURCE QUEUE pg_default WITH (ACTIVE_STATEMENTS=20);\nALTER RESOURCE GROUP admin_group SET CPU_MAX_PERCENT 1;\nALTER RESOURCE GROUP admin_group SET CPU_WEIGHT 100;\nALTER RESOURCE GROUP default_group SET CPU_MAX_PERCENT 1;\nALTER RESOURCE GROUP default_group SET CPU_WEIGHT 100;\nALTER RESOURCE GROUP system_group SET CPU_MAX_PERCENT 1;\nALTER RESOURCE GROUP system_group SET CPU_WEIGHT 100;\nALTER RESOURCE GROUP default_group SET CPU_WEIGHT 100;\nALTER RESOURCE GROUP default_group SET CONCURRENCY 20;\nALTER RESOURCE GROUP default_group SET CPU_MAX_PERCENT 20;\nALTER RESOURCE GROUP admin_group SET CPU_WEIGHT 100;\nALTER RESOURCE GROUP admin_group SET CONCURRENCY 10;\nALTER RESOURCE GROUP admin_group SET CPU_MAX_PERCENT 10;\nALTER RESOURCE GROUP system_group SET CPU_WEIGHT 100;\nALTER RESOURCE GROUP system_group SET CONCURRENCY 0;\nALTER RESOURCE GROUP system_group SET CPU_MAX_PERCENT 10;\nCREATE ROLE gpadmin;\nALTER ROLE gpadmin WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION PASSWORD &#039;md5b44a9b06d576a0b083cd60e5f875cf48&#039; RESOURCE QUEUE pg_default RESOURCE GROUP admin_group;\nCREATE DATABASE d TEMPLATE template0;\nALTER DATABASE d OWNER TO gpadmin;\nCOMMENT ON SCHEMA public IS &#039;standard public schema&#039;;\nALTER SCHEMA public OWNER TO gpadmin;\nREVOKE ALL ON SCHEMA public FROM PUBLIC;\nREVOKE ALL ON SCHEMA public FROM gpadmin;\nGRANT ALL ON SCHEMA public TO PUBLIC;\nGRANT ALL ON SCHEMA public TO gpadmin;\nCREATE SCHEMA IF NOT EXISTS gp_toolkit;\nSET search_path=gp_toolkit,pg_catalog;\nCREATE EXTENSION IF NOT EXISTS gp_toolkit WITH SCHEMA gp_toolkit;\nSET search_path=pg_catalog;\nCOMMENT ON EXTENSION gp_toolkit IS &#039;various GPDB administrative views\/functions&#039;;\nCREATE TABLE public.t1 (\n        id integer NOT NULL,\n        dummy text\n) DISTRIBUTED BY (id);\nALTER TABLE public.t1 OWNER TO gpadmin;\nALTER TABLE ONLY public.t1 ADD CONSTRAINT t1_pkey PRIMARY KEY (id);\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\">The local backup directory on the coordinator does not contain any user data, only meta data:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: bash; highlight: [1]; title: ; notranslate\" title=\"\">\n&#x5B;gpadmin@cdw ~]$ ls -la backup\/gpseg-1\/backups\/20240301\/20240301110143\/\ntotal 16\ndrwxr-xr-x 2 gpadmin gpadmin  171 Mar  1 11:01 .\ndrwxr-xr-x 3 gpadmin gpadmin   28 Mar  1 11:01 ..\n-r--r--r-- 1 gpadmin gpadmin  742 Mar  1 11:01 gpbackup_20240301110143_config.yaml\n-r--r--r-- 1 gpadmin gpadmin 1935 Mar  1 11:01 gpbackup_20240301110143_metadata.sql\n-r--r--r-- 1 gpadmin gpadmin 1965 Mar  1 11:01 gpbackup_20240301110143_report\n-r--r--r-- 1 gpadmin gpadmin 4048 Mar  1 11:01 gpbackup_20240301110143_toc.yaml\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\">The actual data is on the segment nodes:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\n&#x5B;gpadmin@sdw1 ~]$ ls backup\/gpseg0\/backups\/20240301\/20240301110143\/\ngpbackup_0_20240301110143_17122.gz\n&#x5B;gpadmin@sdw2 ~]$ ls  backup\/gpseg1\/backups\/20240301\/20240301110143\/\ngpbackup_1_20240301110143_17122.gz\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\">Restoring that is done with &#8220;gprestore&#8221; passing in the timestamp (the directory name) of the backup:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: bash; highlight: [1]; title: ; notranslate\" title=\"\">\n&#x5B;gpadmin@cdw ~]$ psql -c &quot;drop database d&quot; postgres\nDROP DATABASE\n&#x5B;gpadmin@cdw ~]$ .\/gprestore --backup-dir ~\/backup\/ --timestamp 20240301110143\n20240301:11:18:41 gprestore:gpadmin:cdw:005223-&#x5B;INFO]:-Restore Key = 20240301110143\n20240301:11:18:41 gprestore:gpadmin:cdw:005223-&#x5B;INFO]:-gpbackup version = 1.30.3\n20240301:11:18:41 gprestore:gpadmin:cdw:005223-&#x5B;INFO]:-gprestore version = 1.30.3\n20240301:11:18:41 gprestore:gpadmin:cdw:005223-&#x5B;INFO]:-Greenplum Database Version = 7.1.0 build commit:e7c2b1f14bb42a1018ac57d14f4436880e0a0515 Open Source\n20240301:11:18:41 gprestore:gpadmin:cdw:005223-&#x5B;CRITICAL]:-Database &quot;d&quot; does not exist. Use the --create-db flag to create &quot;d&quot; as part of the restore process.\n20240301:11:18:41 gprestore:gpadmin:cdw:005223-&#x5B;INFO]:-Found neither \/usr\/local\/greenplum-db-7.1.0\/bin\/gp_email_contacts.yaml nor \/home\/gpadmin\/gp_email_contacts.yaml\n20240301:11:18:41 gprestore:gpadmin:cdw:005223-&#x5B;INFO]:-Email containing gprestore report \/home\/gpadmin\/backup\/gpseg-1\/backups\/20240301\/20240301110143\/gprestore_20240301110143_20240301111841_report will not be sent\n&#x5B;gpadmin@cdw ~]$ .\/gprestore --backup-dir ~\/backup\/ --timestamp 20240301110143 --create-db\n20240301:11:18:47 gprestore:gpadmin:cdw:005245-&#x5B;INFO]:-Restore Key = 20240301110143\n20240301:11:18:47 gprestore:gpadmin:cdw:005245-&#x5B;INFO]:-gpbackup version = 1.30.3\n20240301:11:18:47 gprestore:gpadmin:cdw:005245-&#x5B;INFO]:-gprestore version = 1.30.3\n20240301:11:18:47 gprestore:gpadmin:cdw:005245-&#x5B;INFO]:-Greenplum Database Version = 7.1.0 build commit:e7c2b1f14bb42a1018ac57d14f4436880e0a0515 Open Source\n20240301:11:18:48 gprestore:gpadmin:cdw:005245-&#x5B;INFO]:-Creating database\n20240301:11:18:52 gprestore:gpadmin:cdw:005245-&#x5B;INFO]:-Database creation complete for: d\n20240301:11:18:52 gprestore:gpadmin:cdw:005245-&#x5B;INFO]:-Restoring pre-data metadata\nPre-data objects restored:  8 \/ 8 &#x5B;=====================================================] 100.00% 0s\n20240301:11:18:52 gprestore:gpadmin:cdw:005245-&#x5B;INFO]:-Pre-data metadata restore complete\nTables restored:  1 \/ 1 &#x5B;==================================================================] 100.00%\n&#x5B;-----------------------------------------------------------------------------------------=]   0.00%\n20240301:11:18:53 gprestore:gpadmin:cdw:005245-&#x5B;INFO]:-Data restore complete\n20240301:11:18:53 gprestore:gpadmin:cdw:005245-&#x5B;INFO]:-Restoring post-data metadata\nPost-data objects restored:  1 \/ 1 &#x5B;====================================================] 100.00% 0s\n20240301:11:18:54 gprestore:gpadmin:cdw:005245-&#x5B;INFO]:-Post-data metadata restore complete\n20240301:11:18:54 gprestore:gpadmin:cdw:005245-&#x5B;INFO]:-Found neither \/usr\/local\/greenplum-db-7.1.0\/bin\/gp_email_contacts.yaml nor \/home\/gpadmin\/gp_email_contacts.yaml\n20240301:11:18:54 gprestore:gpadmin:cdw:005245-&#x5B;INFO]:-Email containing gprestore report \/home\/gpadmin\/backup\/gpseg-1\/backups\/20240301\/20240301110143\/gprestore_20240301110143_20240301111847_report will not be sent\n20240301:11:18:54 gprestore:gpadmin:cdw:005245-&#x5B;INFO]:-Restore completed successfully\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\">Of course you should make sure that the backup directories are separate mount points and are not local on the nodes. There are also some <a href=\"https:\/\/docs.vmware.com\/en\/VMware-Greenplum-Backup-and-Restore\/1.30\/greenplum-backup-and-restore\/admin_guide-managing-backup-plugins.html\" target=\"_blank\" rel=\"noreferrer noopener\">storage plugins<\/a> you might want to consider.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">According to the documentation you should not use <a href=\"https:\/\/docs.vmware.com\/en\/VMware-Greenplum\/7\/greenplum-database\/utility_guide-about-utils.html?hWord=N4IghgNiBcIA4HMD6AjMBnApmgxgawFc4QBfIA\" target=\"_blank\" rel=\"noreferrer noopener\">pg_basebackup<\/a> to backup segment instances, so doing physical backups and point in time recoveries is not an option. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">In the next post we&#8217;ll look at how we can recover from a failed segment node.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>This is the fourth part of the Greenplum blog series, the previous ones are here: Getting started with Greenplum \u2013 1 \u2013 Installation, Getting started with Greenplum \u2013 2 \u2013 Initializing and bringing up the cluster, Getting started with Greenplum \u2013 3 \u2013 Behind the scenes. In this blog we&#8217;ll look at how you are [&hellip;]<\/p>\n","protected":false},"author":28,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229,198],"tags":[3276,77],"type_dbi":[],"class_list":["post-31553","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","category-database-management","tag-greenplum","tag-postgresql"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v28.8 (Yoast SEO v28.8) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>Getting started with Greenplum \u2013 4 \u2013 Backup &amp; Restore - databases - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/getting-started-with-greenplum-4-backup-restore-databases\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Getting started with Greenplum \u2013 4 \u2013 Backup &amp; Restore - databases\" \/>\n<meta property=\"og:description\" content=\"This is the fourth part of the Greenplum blog series, the previous ones are here: Getting started with Greenplum \u2013 1 \u2013 Installation, Getting started with Greenplum \u2013 2 \u2013 Initializing and bringing up the cluster, Getting started with Greenplum \u2013 3 \u2013 Behind the scenes. In this blog we&#8217;ll look at how you are [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/getting-started-with-greenplum-4-backup-restore-databases\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2024-03-04T10:15:33+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-03-07T09:53:47+00:00\" \/>\n<meta name=\"author\" content=\"Open source Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Open source Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/getting-started-with-greenplum-4-backup-restore-databases\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/getting-started-with-greenplum-4-backup-restore-databases\\\/\"},\"author\":{\"name\":\"Open source Team\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/4bae5e5dd8c7d48edbd948772b49b202\"},\"headline\":\"Getting started with Greenplum \u2013 4 \u2013 Backup &amp; Restore &#8211; databases\",\"datePublished\":\"2024-03-04T10:15:33+00:00\",\"dateModified\":\"2024-03-07T09:53:47+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/getting-started-with-greenplum-4-backup-restore-databases\\\/\"},\"wordCount\":527,\"commentCount\":0,\"keywords\":[\"Greenplum\",\"PostgreSQL\"],\"articleSection\":[\"Database Administration &amp; Monitoring\",\"Database management\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/getting-started-with-greenplum-4-backup-restore-databases\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/getting-started-with-greenplum-4-backup-restore-databases\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/getting-started-with-greenplum-4-backup-restore-databases\\\/\",\"name\":\"Getting started with Greenplum \u2013 4 \u2013 Backup &amp; Restore - databases - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2024-03-04T10:15:33+00:00\",\"dateModified\":\"2024-03-07T09:53:47+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/4bae5e5dd8c7d48edbd948772b49b202\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/getting-started-with-greenplum-4-backup-restore-databases\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/getting-started-with-greenplum-4-backup-restore-databases\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/getting-started-with-greenplum-4-backup-restore-databases\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Getting started with Greenplum \u2013 4 \u2013 Backup &amp; Restore &#8211; databases\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/4bae5e5dd8c7d48edbd948772b49b202\",\"name\":\"Open source Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"caption\":\"Open source Team\"},\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/open-source-team\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Getting started with Greenplum \u2013 4 \u2013 Backup &amp; Restore - databases - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/getting-started-with-greenplum-4-backup-restore-databases\/","og_locale":"en_US","og_type":"article","og_title":"Getting started with Greenplum \u2013 4 \u2013 Backup &amp; Restore - databases","og_description":"This is the fourth part of the Greenplum blog series, the previous ones are here: Getting started with Greenplum \u2013 1 \u2013 Installation, Getting started with Greenplum \u2013 2 \u2013 Initializing and bringing up the cluster, Getting started with Greenplum \u2013 3 \u2013 Behind the scenes. In this blog we&#8217;ll look at how you are [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/getting-started-with-greenplum-4-backup-restore-databases\/","og_site_name":"dbi Blog","article_published_time":"2024-03-04T10:15:33+00:00","article_modified_time":"2024-03-07T09:53:47+00:00","author":"Open source Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Open source Team","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/getting-started-with-greenplum-4-backup-restore-databases\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/getting-started-with-greenplum-4-backup-restore-databases\/"},"author":{"name":"Open source Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/4bae5e5dd8c7d48edbd948772b49b202"},"headline":"Getting started with Greenplum \u2013 4 \u2013 Backup &amp; Restore &#8211; databases","datePublished":"2024-03-04T10:15:33+00:00","dateModified":"2024-03-07T09:53:47+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/getting-started-with-greenplum-4-backup-restore-databases\/"},"wordCount":527,"commentCount":0,"keywords":["Greenplum","PostgreSQL"],"articleSection":["Database Administration &amp; Monitoring","Database management"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/getting-started-with-greenplum-4-backup-restore-databases\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/getting-started-with-greenplum-4-backup-restore-databases\/","url":"https:\/\/www.dbi-services.com\/blog\/getting-started-with-greenplum-4-backup-restore-databases\/","name":"Getting started with Greenplum \u2013 4 \u2013 Backup &amp; Restore - databases - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2024-03-04T10:15:33+00:00","dateModified":"2024-03-07T09:53:47+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/4bae5e5dd8c7d48edbd948772b49b202"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/getting-started-with-greenplum-4-backup-restore-databases\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/getting-started-with-greenplum-4-backup-restore-databases\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/getting-started-with-greenplum-4-backup-restore-databases\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Getting started with Greenplum \u2013 4 \u2013 Backup &amp; Restore &#8211; databases"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/4bae5e5dd8c7d48edbd948772b49b202","name":"Open source Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","caption":"Open source Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/open-source-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/31553","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/28"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=31553"}],"version-history":[{"count":17,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/31553\/revisions"}],"predecessor-version":[{"id":31690,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/31553\/revisions\/31690"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=31553"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=31553"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=31553"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=31553"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}