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 1iiF3x-0007u2-CE for pgsql-admin@arkaria.postgresql.org; Fri, 20 Dec 2019 09:59: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 1iiF3w-00028C-0z for pgsql-admin@arkaria.postgresql.org; Fri, 20 Dec 2019 09:59:12 +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 1iiF3v-000285-IE for pgsql-admin@lists.postgresql.org; Fri, 20 Dec 2019 09:59:11 +0000 Received: from mail-ed1-x544.google.com ([2a00:1450:4864:20::544]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1iiF3s-0007PG-TF for pgsql-admin@lists.postgresql.org; Fri, 20 Dec 2019 09:59:11 +0000 Received: by mail-ed1-x544.google.com with SMTP id f8so7691170edv.2 for ; Fri, 20 Dec 2019 01:59:03 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=zunibal-com.20150623.gappssmtp.com; s=20150623; h=subject:to:references:from:message-id:date:user-agent:mime-version :in-reply-to:content-language; bh=dV85/a3f5eLPpDmLDtyObwXhSMvV+Py0qkSHooxmdXA=; b=DF1beEqRSu+/W8cqR4WWl5yHp4yZJJ81CGWuwmkU5qms3nMo+YZ9QPrkoxxNwOWFsF pUqaXzvlEmMIC1BPfja8VQ/Kd2aqbMFKr76N1maRECZ8d+lTNbpkQ55BENDQTMQIf/Mp XmZGJO4X5H1QMoeu0R1IZXX0KcOCmxlxccO16MzzLBaMEdSPIlGpeHFZXuSF5Aejghul an9Qbv8NXekG4NRBgIYi1/7m6ou7CwUiaxivXb6wP1+sMbvhcz9h3w96GN4yhtj3k3O1 XbTDztbFCrC1qmvqFEsDZLw5HKcyE2WfnA64MOB51WoeoxINesfZI52lu3f+XSa5MGcO bTbQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:subject:to:references:from:message-id:date :user-agent:mime-version:in-reply-to:content-language; bh=dV85/a3f5eLPpDmLDtyObwXhSMvV+Py0qkSHooxmdXA=; b=SWPRGE/DdG3+vC6zOmDkN6LpMkHn1E5qLOt46L3mlYtEcWgF0LpzTPBCF5wdY9mBEt 13fFwunHfD4D3Ml0ta2Ofr7aY6IKcvxxEn8NlPNwrAsswka2XEs4Kp9fIGj8CBZ8Tcoq 0mMZdP7bo6PbiUeToFffdqpS846KocutYonkNtN78ogpiUc5Apt7qFMS2Ida6Z6RCVB1 ziW/5PgDFjJP78ERLLBy7J4tJEdOXOnWZy5ciXnN+Ij+N4p4ndM7sYlqExIQ4gywVejm SlpyA7ZMCgPqAao6vCWswjo4GD2qsjsvm7ZJcHTzXHTDf9TJIin2ycQr/b49qb2bRfFr dfdA== X-Gm-Message-State: APjAAAWa+H3Q6G782r6wrbgqvhKXBFXmM7ZG8SKgLoJ8DBx1qVEYq59F G0yumdTAGXAeTf67YaSNCfcGAGn2wa/ARQ== X-Google-Smtp-Source: APXvYqwizGpHziO1Bhm7h8ji0SDJdGXU8gvO247S9O3bAQnLLQ8Bu1gJh7jZc/bIFlFT2e8BJg0jaw== X-Received: by 2002:a50:fd93:: with SMTP id o19mr14894167edt.28.1576835941590; Fri, 20 Dec 2019 01:59:01 -0800 (PST) Received: from [192.168.3.150] ([176.222.91.85]) by smtp.gmail.com with ESMTPSA id n14sm1007494ejx.11.2019.12.20.01.59.00 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Fri, 20 Dec 2019 01:59:01 -0800 (PST) Subject: Re: backup script error with could not connect to database To: pgsql-admin@lists.postgresql.org References: <280013024.631417.1576695326572.ref@mail.yahoo.com> <280013024.631417.1576695326572@mail.yahoo.com> <700937906.892189.1576773893896@mail.yahoo.com> From: Ekaterina Amez Message-ID: <2c818ef8-e555-6dea-a5e8-cd059a3c9ebd@zunibal.com> Date: Fri, 20 Dec 2019 10:59:00 +0100 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101 Thunderbird/68.2.2 MIME-Version: 1.0 In-Reply-To: <700937906.892189.1576773893896@mail.yahoo.com> Content-Type: multipart/alternative; boundary="------------111C8F2F749E5C237E9EB410" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------111C8F2F749E5C237E9EB410 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit El 19/12/19 a las 17:44, Pepe TD Vo escribió: > I made it work.  I changed all in pg_hba.conf to trust and backup > scripts run fine. When Postgres searches for the matching line in pg_hba.conf, the first line that matches current connection wins. This means that if you have:     host  all  all                      127.0.0.1/32 indent     host  replication  all        127.0.0.1/32          trust The first line wins and indent is applied instead of trust. > thank you so much. > > ** > *Bach-Nga > > *No one in this world is pure and perfect.  If you avoid people for > their mistakes you will be alone. So judge less, love and forgive more. > To call him a dog hardly seems to do him justice though in as much as > he had four legs, a tail, and barked, I admit he was, to all outward > appearances. But to those who knew him well, he was a perfect > gentleman (Hermione Gingold) > > **Live simply **Love generously **Care deeply **Speak kindly. > *** Genuinely rich *** Faithful talent *** Sharing success > > > On Thursday, December 19, 2019, 07:12:54 AM EST, bw > wrote: > > > Did you try > > root su -c postgres pg_dumpall > postgresdump.sql > > On Wed, 18 Dec 2019 at 19:55, Pepe TD Vo > wrote: > > > > hello expert, > > > > I had to do a backup on replication master-slave servers.  I also > have asked this question before, but  I don't understand why I > followed the information and same scripting to run on the replication > servers and didn't work.  I can run pg_dumpall successful but not with > the script.  Would you please tell me what's wrong and what need to > correct the error? > > > > my replication server (master) pg_hba.conf > > > > # "local" is for Unix domain socket connections only > > local  all  all                 peer > > host  all    all                    192.168.2.0/24       trust > > # IPv4 local connections: > > host  all  all                      127.0.0.1/32       indent > > local  all  postgres             trust > > host  all  power_user        0.0.0.0/0 md5 > > host  all  other_user          0.0.0.0/0     md5 > > host  all  storageLoader    0.0.0.0/0   md5 > > # IPv6 local connections: > > host  all  all                        ::1/128             indent > > #allow replication connections from localhost, by a user with the > replication priviledge > > local  replication  all             trust > > host  replication  all        127.0.0.1/32 trust > > host  replicaiton  all        ::1/128       trust > > host  replication  replicauser    masterIP/32 trust > > host  replicaiton  replicauser    slaveIP/32 trust > > > > from the postgres prompt I can connect to each database > > > > #psql -d template0 -U postgres > > template0-# > > #psql -d template1 -U postgres > > template1-# > > > > #psql -d pscidr -U postgres > > pscidr-# > > #psql -d postgres -U postgres > > postgres-# > > > > > > when I run the script as postgres os, I don't see the error but the > only global.sql backup but nothing in it, 0 byte (blank) sql script. > > > > when I run as root su -c postgres /path/pg_backup.sh > pgbackup.log, > I get below: > > > > Performing globals backup: > > Globals backup > > pg_dumpall: could not connect to database "template1":FATAL: Ident > authentication failed for user "postgres" fro globals backup. > > > > Performing schema-only backup: > > psql:FATAL: Ident authentication failed for user "postgres" > > the following databases were matched for schema-only backup: > > > > performing full backup: > > psql:FATAL: Ident authentication failed for user "postgres" > > > > > > I can run pg_dumpall > postgresdump.sql as postgres user os fine > will all the databases backup > > > > pg_backup.sh and pg_backup.config attachment. > > > > v/r, > > > > Bach-Nga > > > > No one in this world is pure and perfect.  If you avoid people for > their mistakes you will be alone. So judge less, love and forgive more. > > To call him a dog hardly seems to do him justice though in as much > as he had four legs, a tail, and barked, I admit he was, to all > outward appearances. But to those who knew him well, he was a perfect > gentleman (Hermione Gingold) > > > > **Live simply **Love generously **Care deeply **Speak kindly. > > *** Genuinely rich *** Faithful talent *** Sharing success > --------------111C8F2F749E5C237E9EB410 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit


El 19/12/19 a las 17:44, Pepe TD Vo escribió:
I made it work.  I changed all in pg_hba.conf to trust and backup scripts run fine.

When Postgres searches for the matching line in pg_hba.conf, the first line that matches current connection wins. This means that if you have:

    host  all  all                      127.0.0.1/32          indent

    host  replication  all        127.0.0.1/32          trust

The first line wins and indent is applied instead of trust.


thank you so much.

Bach-Nga

No one in this world is pure and perfect.  If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.
To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)

**Live simply **Love generously **Care deeply **Speak kindly.
*** Genuinely rich *** Faithful talent *** Sharing success


On Thursday, December 19, 2019, 07:12:54 AM EST, bw <bwakkie@gmail.com> wrote:


Did you try

root su -c postgres pg_dumpall > postgresdump.sql

On Wed, 18 Dec 2019 at 19:55, Pepe TD Vo <pepevo@yahoo.com> wrote:
>
> hello expert,
>
> I had to do a backup on replication master-slave servers.  I also have asked this question before, but  I don't understand why I followed the information and same scripting to run on the replication servers and didn't work.  I can run pg_dumpall successful but not with the script.  Would you please tell me what's wrong and what need to correct the error?
>
> my replication server (master) pg_hba.conf
>
> # "local" is for Unix domain socket connections only
> local  all  all                                                    peer
> host  all    all                    192.168.2.0/24        trust
> # IPv4 local connections:
> host  all  all                      127.0.0.1/32          indent
> local  all  postgres                                            trust
> host  all  power_user        0.0.0.0/0              md5
> host  all  other_user          0.0.0.0/0                md5
> host  all  storageLoader    0.0.0.0/0                md5
> # IPv6 local connections:
> host  all  all                        ::1/128                  indent
> #allow replication connections from localhost, by a user with the replication priviledge
> local  replication  all                                        trust
> host  replication  all        127.0.0.1/32          trust
> host  replicaiton  all        ::1/128                    trust
> host  replication  replicauser    masterIP/32  trust
> host  replicaiton  replicauser    slaveIP/32    trust
>
> from the postgres prompt I can connect to each database
>
> #psql -d template0 -U postgres
> template0-#
> #psql -d template1 -U postgres
> template1-#
>
> #psql -d pscidr -U postgres
> pscidr-#
> #psql -d postgres -U postgres
> postgres-#
>
>
> when I run the script as postgres os, I don't see the error but the only global.sql backup but nothing in it, 0 byte (blank) sql script.
>
> when I run as root su -c postgres /path/pg_backup.sh > pgbackup.log, I get below:
>
> Performing globals backup:
> Globals backup
> pg_dumpall: could not connect to database "template1":FATAL: Ident authentication failed for user "postgres" fro globals backup.
>
> Performing schema-only backup:
> psql:FATAL: Ident authentication failed for user "postgres"
> the following databases were matched for schema-only backup:
>
> performing full backup:
> psql:FATAL: Ident authentication failed for user "postgres"
>
>
> I can run pg_dumpall > postgresdump.sql as postgres user os fine will all the databases backup
>
> pg_backup.sh and pg_backup.config attachment.
>
> v/r,
>
> Bach-Nga
>
> No one in this world is pure and perfect.  If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.
> To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)
>
> **Live simply **Love generously **Care deeply **Speak kindly.
> *** Genuinely rich *** Faithful talent *** Sharing success

--------------111C8F2F749E5C237E9EB410--