Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1j1ovJ-0005o4-0T for pgsql-general@arkaria.postgresql.org; Wed, 12 Feb 2020 10:07:13 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1j1ovH-0006KX-JC for pgsql-general@arkaria.postgresql.org; Wed, 12 Feb 2020 10:07:11 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1j1ovH-0006KP-99 for pgsql-general@lists.postgresql.org; Wed, 12 Feb 2020 10:07:11 +0000 Received: from portal-smtp-out.hot.ee ([194.126.101.125]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1j1ovE-0008Fz-Nw for pgsql-general@postgresql.org; Wed, 12 Feb 2020 10:07:10 +0000 Received: from localhost (localhost [127.0.0.1]) by vm-hot-relay10.estpak.ee (Postfix) with ESMTP id B94601B25; Wed, 12 Feb 2020 12:07:07 +0200 (EET) X-Virus-Scanned: Debian amavisd-new at vm-hot-relay10.estpak.ee Received: from portal-smtp-out.hot.ee ([127.0.0.1]) by localhost (vm-hot-relay10.estpak.ee [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id FSukjsFyF1st; Wed, 12 Feb 2020 12:07:05 +0200 (EET) Received: from mail.hot.ee (hot-relayhost1.estpak.ee [213.180.2.79]) by vm-hot-relay10.estpak.ee (Postfix) with ESMTP id 76EA41B7B; Wed, 12 Feb 2020 12:07:05 +0200 (EET) Received: from dell2 (66-63-196-88.sta.estpak.ee [88.196.63.66]) (Authenticated sender: kobruleht2) by HOT-Relayhost1.estpak.ee (Postfix) with ESMTPSA id 48Hb0Q6L3mz2Tc; Wed, 12 Feb 2020 12:07:01 +0200 (EET) Message-ID: <6316D0244B55410388A5E0FC387B006E@dell2> From: "Andrus" To: "Justin" , "Tom Lane" Cc: "pgsql-general" References: <38D406CB287140A49F635258B634E072@dell2> <11491.1581461180@sss.pgh.pa.us> In-Reply-To: Subject: Re: How to restore roles without changing postgres password Date: Wed, 12 Feb 2020 12:06:56 +0200 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_022C_01D5E19C.EBC212D0" X-Priority: 3 X-MSMail-Priority: Normal Importance: Normal X-Mailer: Microsoft Windows Live Mail 16.4.3528.331 X-MimeOLE: Produced By Microsoft MimeOLE V16.4.3528.331 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk See on mitmeosaline MIME-vormingus sõnum. ------=_NextPart_000_022C_01D5E19C.EBC212D0 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hi! >Not a bad idea, would want to extend this to all the roles on the = server not just postgres =20 >I've edited the global dump many times removing/editing table spaces, = comment old users, etc.. =20 Maybe it is easier to create plpgsql procedure which returns desired = script as text. Or it retrieves globals from other cluster using dblink and applies = changes to new cluster. This can be called instead of pq_dumpall and can edited for custom = needs. Editing plpgsql script is easier for postgres users than creating sed = script to delete commands from sql file. Andrus. ------=_NextPart_000_022C_01D5E19C.EBC212D0 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

Hi!

>Not a bad idea, would want to extend this to all the = roles on the=20 server not just postgres

>I've edited the global dump many times = removing/editing=20 table spaces, comment old users, etc..

Maybe it is easier to create plpgsql procedure which returns = desired script=20 as text.

Or it retrieves globals from other cluster using dblink and applies = changes=20 to new cluster.

This can be called instead of pq_dumpall and can edited for custom=20 needs.

Editing plpgsql script is easier for postgres users than creating = sed=20 script to delete commands from sql file.

Andrus.

= ------=_NextPart_000_022C_01D5E19C.EBC212D0--