Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ow3LA-00082V-F6 for pgsql-general@arkaria.postgresql.org; Fri, 18 Nov 2022 15:31:40 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1ow3L9-00042i-C9 for pgsql-general@arkaria.postgresql.org; Fri, 18 Nov 2022 15:31:39 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ow3L9-00042Z-0m for pgsql-general@lists.postgresql.org; Fri, 18 Nov 2022 15:31:39 +0000 Received: from mail-qt1-x832.google.com ([2607:f8b0:4864:20::832]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1ow3L6-0007pS-RB for pgsql-general@lists.postgresql.org; Fri, 18 Nov 2022 15:31:38 +0000 Received: by mail-qt1-x832.google.com with SMTP id s4so3314840qtx.6 for ; Fri, 18 Nov 2022 07:31:36 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=YKVrWAN6UpaT9hUfCc1xgTtWSjYboHrj15euL35sqbc=; b=RSG6KFGTFHL6xQR1tEk+H4TExA1S8s/DG8tvMXuK3wGrlVq29H8LUw3qtE/jlo6fMu v/iUZsBaKT1/1zHI7KiHHSxgaU1kxqxbqnMGt1pX3+dOVeIHvbxyB1sKKpMw+p77exG9 BWEjRDDaW7sj6A+BtOXvLarqxIAuyBYcwQnu0/MNa6elC62TyK0Tmn1odOrWm12yUx12 /sF8JBTFuIiuL75/ayckyl2zvqWaxFYzPR1D6/M2T0SCvVIoMM+O5WKfUKHGUgu7OPKf 2ZHppAPXPybQ/uGKv1DHTLMtdh79hIGhTA/V/E2qiEG1i4NdA6y4Br5QukHZzMuPXkLu HpZg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=YKVrWAN6UpaT9hUfCc1xgTtWSjYboHrj15euL35sqbc=; b=nPn/buQ1H2+LOKE4NrxwUZ1YQRO3+pRsMubACfgXHw+L0GckDJ8KtgZbl//KBl0biw KrcyQpxYOoFG/JJMg7upcAZLrgEV+5rslyiRXMMokPBzeDpOkcfQ5tzIbfdw+2EgggnS QZ940Rj7LjyPSDtngm0IyTFTcDktVdFhxkFVvjZpRFgCRxqkumOplrzvhNL1o4XS5uT4 9ocuaf5BONanlnBNNU07w6Ft8zvKSLFoFMLNteBUgjuGSo0m/9s4ipyyGygupB14hq3C B2kN0MXPckRNoZ5SOIYHj26VT/h7QMeR3Eec8KnNLmwlBKzRU5iXj+jW5j/x7qstMVrr vOMw== X-Gm-Message-State: ANoB5pmhVSAoUrVN5H/HGdtTG9ggln/T1dGpiuAgoeC2YoQR5zW1zTS+ srgFbjJdrX673pVu57fnTboaUM94b6U= X-Google-Smtp-Source: AA0mqf7Js4NSJpM5jl47NlG5TWD5S25aElT9K8bP/XDXMaVFPNwDlSKQ668bvuWEUsUFjYVeFLTNKw== X-Received: by 2002:a05:622a:5c96:b0:3a5:2bae:77e9 with SMTP id ge22-20020a05622a5c9600b003a52bae77e9mr7065162qtb.318.1668785494713; Fri, 18 Nov 2022 07:31:34 -0800 (PST) Received: from ?IPV6:2600:1700:bd2c:b810::43? ([2600:1700:bd2c:b810::43]) by smtp.googlemail.com with ESMTPSA id f1-20020ac84641000000b00343057845f7sm2130981qto.20.2022.11.18.07.31.34 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Fri, 18 Nov 2022 07:31:34 -0800 (PST) Content-Type: multipart/alternative; boundary="------------0kQm8X0CFtXx9meG4cIA2VOC" Message-ID: Date: Fri, 18 Nov 2022 09:31:33 -0600 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.2.2 Subject: Re: copy databases from two differend backups to one cluster Content-Language: en-US To: pgsql-general@lists.postgresql.org References: From: Ron In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------0kQm8X0CFtXx9meG4cIA2VOC Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 11/18/22 06:33, Joseph Kennedy wrote: > > I would like to copy databases from two different pg_basebackup backups to > one lab environment database cluster on another server. Is it possible ? > > It's a good idea to do it in that way. > > Databases has your oid in pg_basebackup we can find it id in base > directory for example: > >  base/ > > 1 > > 125859 > > 14042 > > 14043 > > 2315410 > > 2321461 > > 2321462 > > 2321463 > > Database OID (and directory name) we can find by : > > SELECT oid from pg_database where datname = 'database name'; > >  Steps: > > create empty databases on destination server (database oid they will be > probably different then on source databases) > > stop Postgresql > > cleanup destination directories eg. 2315410, 2321461, 2321462, 2321463 > > copy eg. content from pg_bassebackup01 base/(some OID) to 2315410 and 2321461 > > copy eg. content from pg_bassebackup02 base/(some OID) to 2321462 and 2321463 > > start postgresql > > > Is it a good or  very bad idea ? > Why can't you do pg_dump/pg_restore? -- Angular momentum makes the world go 'round. --------------0kQm8X0CFtXx9meG4cIA2VOC Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit On 11/18/22 06:33, Joseph Kennedy wrote:

I would like to copy databases from two different pg_basebackup backups to one lab environment database cluster on another server. Is it possible ?

It's a good idea to do it in that way.

Databases has your oid in pg_basebackup we can find it id in base directory for example:

 base/

1

125859

14042

14043

2315410

2321461

2321462

2321463

Database OID (and directory name) we can find by :

SELECT oid from pg_database where datname = 'database name';

 Steps:

create empty databases on destination server (database oid they will be probably different then on source databases)

stop Postgresql

cleanup destination directories eg. 2315410, 2321461, 2321462, 2321463

copy eg. content from pg_bassebackup01 base/(some OID) to 2315410 and 2321461

copy eg. content from pg_bassebackup02 base/(some OID) to 2321462 and 2321463

start postgresql


Is it a good or  very bad idea ?


Why can't you do pg_dump/pg_restore?

--
Angular momentum makes the world go 'round.
--------------0kQm8X0CFtXx9meG4cIA2VOC--