Re: question about zeroes in the wal file names
On 2019-08-18 16:17, Luca Ferrari wrote: > I'm just curious to better understand the naming convention behind wal > files, because I've seen on a system of mine that the wals created > were: > > 0005020E00FF > 0005020F > > while I was expecting 20E0x100. You are in principle correct. This naming system is a historical accident. The actual LSN associated with the first file is 020EFF00 and so the next one is naturally 020F The reason the zeroes are in there comes from a time when PostgreSQL didn't fully support 64-bit integers, and the LSNs and the files were tracked internally as pairs of 32-bit integers. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
pg_basebackup exit codes
Hi,
I was unpleasantly surprised to see that pg_basebackup will return a zero
(Success) exit code even when it has actually failed like below:
pg_basebackup: could not write to compressed file
"/var/lib/pgsql/10/inst1/backup/cust-pg02_inst1_Fri_20190816/base.tar.gz": No
space left on device
pg_basebackup: removing data directory
"/var/lib/pgsql/10/inst1/backup/cust-pg02_inst1_Fri_20190816"
A reliable exit code is of great importance in backup scripts using a statement
like below:
pg_basebackup -D ${backup_dest} -Ft -z 2>&1 | tee -a ${log_file}; rv=$?
[ $rv -eq 0 ] || error "pg_basebackup failed with rcode $rv; also see
${log_file}"
Pg_basebackup version: (PostgreSQL) 10.10
Anybody else encountering this problem?
Regards, John
postmaster utilization
Hi, We have scheduled postgres full backup on centos 7 machine. DB size is around 66 GB. We observed while backup is running, postmaster CPU % reaches to 90 - 100%,which results very strange behavior: that we are not able to perform any DDL and DML on to the database during the time. Please suggest a way to limit it's CPU utilization or else let us know how we can access the database during the backup window. Please let us know on priority since this is our prod database. Get back to us in case of any query/detail. Regards, Shiwangini
Re: postmaster utilization
On 8/19/19 5:40 AM, Shiwangini Shishulkar wrote: Hi, We have scheduled postgres full backup on centos 7 machine. DB size is around 66 GB. We observed while backup is running, postmaster CPU % reaches to 90 - 100%,which results very strange behavior: that we are not able to perform any DDL and DML on to the database during the time. Please suggest a way to limit it's CPU utilization or else let us know how we can access the database during the backup window. Please let us know on priority since this is our prod database. Are you compressing the backup? How many threads are you running? How many CPUs on your system? What version of Postgres? -- Angular momentum makes the world go 'round.
Re: pg_basebackup exit codes
On Mon, Aug 19, 2019 at 12:18 PM John Donath wrote:
> Hi,
>
>
>
> I was unpleasantly surprised to see that pg_basebackup will return a zero
> (Success) exit code even when it has actually failed like below:
>
>
>
> pg_basebackup: could not write to compressed file
> "/var/lib/pgsql/10/inst1/backup/cust-pg02_inst1_Fri_20190816/base.tar.gz":
> No space left on device
>
> pg_basebackup: removing data directory
> "/var/lib/pgsql/10/inst1/backup/cust-pg02_inst1_Fri_20190816"
>
>
>
> A reliable exit code is of great importance in backup scripts using a
> statement like below:
>
>
>
> pg_basebackup -D ${backup_dest} -Ft -z 2>&1 | tee -a ${log_file}; rv=$?
>
> [ $rv -eq 0 ] || error "pg_basebackup failed with rcode $rv; also see
> ${log_file}"
>
>
>
> Pg_basebackup version: (PostgreSQL) 10.10
>
>
>
> Anybody else encountering this problem?
>
>
>
I think your problem is that you are looking at the exit code from "tee"
and not from pg_basebackup. If you are using bash, you can look at
something like $PIPESTATUS to get the exit code from the actual comman
dbefore the pipe. Or you can try removing the tee command to verify if this
is indeed where the problem is coming from.
--
Magnus Hagander
Me: https://www.hagander.net/
RE: pg_basebackup exit codes
Thanks a lot Magnus!
I should have known better … ☺
But thanks for the PIPESTATUS hint which comes in uttermost handy
Regards, John
Van: Magnus Hagander
Verzonden: maandag 19 augustus 2019 13:46
Aan: John Donath
CC: pgsql-general@lists.postgresql.org
Onderwerp: Re: pg_basebackup exit codes
On Mon, Aug 19, 2019 at 12:18 PM John Donath
mailto:john.don...@detron.nl>> wrote:
Hi,
I was unpleasantly surprised to see that pg_basebackup will return a zero
(Success) exit code even when it has actually failed like below:
pg_basebackup: could not write to compressed file
"/var/lib/pgsql/10/inst1/backup/cust-pg02_inst1_Fri_20190816/base.tar.gz": No
space left on device
pg_basebackup: removing data directory
"/var/lib/pgsql/10/inst1/backup/cust-pg02_inst1_Fri_20190816"
A reliable exit code is of great importance in backup scripts using a statement
like below:
pg_basebackup -D ${backup_dest} -Ft -z 2>&1 | tee -a ${log_file}; rv=$?
[ $rv -eq 0 ] || error "pg_basebackup failed with rcode $rv; also see
${log_file}"
Pg_basebackup version: (PostgreSQL) 10.10
Anybody else encountering this problem?
I think your problem is that you are looking at the exit code from "tee" and
not from pg_basebackup. If you are using bash, you can look at something like
$PIPESTATUS to get the exit code from the actual comman dbefore the pipe. Or
you can try removing the tee command to verify if this is indeed where the
problem is coming from.
--
Magnus Hagander
Me:
https://www.hagander.net/
Work:
https://www.redpill-linpro.com/
Re: postmaster utilization
Greetings, * Ron (ronljohnso...@gmail.com) wrote: > On 8/19/19 5:40 AM, Shiwangini Shishulkar wrote: > >We have scheduled postgres full backup on centos 7 machine. DB size is > >around 66 GB. We observed while backup is running, postmaster CPU % > >reaches to 90 - 100%,which results very strange behavior: that we are not > >able to perform any DDL and DML on to the database during the time. > >Please suggest a way to limit it's CPU utilization or else let us know how > >we can access the database during the backup window. Please let us know on > >priority since this is our prod database. > > Are you compressing the backup? > How many threads are you running? > How many CPUs on your system? > What version of Postgres? ... What are you using to run the backup? Thanks, Stephen signature.asc Description: PGP signature
Sorry, real newbie question about connecting to a database
I am developinng an appliction usig Postgresql 11, installed on a UBUTU 18.14 machine. I ahve vreated a new database to do some testing on restricting access of specific users/roles to certain data. I have done the following: REVOKE ALL ON DATABASE pertest FROM employee; GRANT CONNECT ON DATABASE pertest TO employee; and I have verifed tht the user employee does exst, I have also doen a few more GRABTs to allow specific acces. But I cannot conect, or swith to user employee: stan@smokey:/etc/postgresql/11/main$ psql -U employee psql: FATAL: Peer authentication failed for user "employee" stan=> \l List of databases Name| Owner | Encoding | Collate | Ctype | Access privileges +--+--+-+-+--- pertest | stan | UTF8 | C.UTF-8 | C.UTF-8 | =Tc/stan + | | stan=CTc/stan employee=CTc/stan Sorrry cut and paste mangled that. What am I failing to do here? -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
Re: Sorry, real newbie question about connecting to a database
stan writes: > But I cannot conect, or swith to user > employee: > stan@smokey:/etc/postgresql/11/main$ psql -U employee > psql: FATAL: Peer authentication failed for user "employee" This means that you've set pg_hba.conf to specify "peer" authentication, which by default only lets you connect as a PG role named the same as your OS account. (This is about are-you-who-you-say-you-are; whether who-you-say-you-are has privilege to connect is independent.) You could use some other auth method, or you could set up a mapping file that says you (stan) are allowed to connect as "employee". regards, tom lane
Re: Sorry, real newbie question about connecting to a database
On 8/19/19 7:44 AM, stan wrote: I am developinng an appliction usig Postgresql 11, installed on a UBUTU 18.14 machine. I ahve vreated a new database to do some testing on restricting access of specific users/roles to certain data. I have done the following: REVOKE ALL ON DATABASE pertest FROM employee; GRANT CONNECT ON DATABASE pertest TO employee; and I have verifed tht the user employee does exst, I have also doen a few more GRABTs to allow specific acces. But I cannot conect, or swith to user employee: stan@smokey:/etc/postgresql/11/main$ psql -U employee psql: FATAL: Peer authentication failed for user "employee" stan=> \l List of databases Name| Owner | Encoding | Collate | Ctype | Access privileges +--+--+-+-+--- pertest | stan | UTF8 | C.UTF-8 | C.UTF-8 | =Tc/stan + | | stan=CTc/stan employee=CTc/stan Sorrry cut and paste mangled that. What am I failing to do here? Tom has spelled out the specific issue. The generic issue is that security in Postgres is a multi-layer process that involves many moving parts. You will save yourself a lot of do overs by looking at the relevant documentation. Starting roughly from outside in: Server connection: https://www.postgresql.org/docs/11/runtime-config-connection.html Client authentication(the pg_hba.conf Tom referred to): https://www.postgresql.org/docs/11/client-authentication.html Database roles(users): https://www.postgresql.org/docs/11/user-manag.html Role/user permissions: https://www.postgresql.org/docs/11/sql-grant.html Finer grained permissions(row level security): https://www.postgresql.org/docs/11/ddl-rowsecurity.html The above is intimidating and not something that will be fully understood in a single reading(or in my case multiple readings:)). Still a passing familiarity with the concepts will make your life easier. -- Adrian Klaver adrian.kla...@aklaver.com
Re: Retroactively adding send and recv functions to a type?
On Tue, Aug 20, 2019 at 1:03 AM Johann 'Myrkraverk' Oskarsson wrote: > The help for ALTER TABLE seems to indicate this is not supported directly. I meant ALTER TYPE. Adding the send and recv functions doesn't seem to supported by ALTER TYPE. Is there a workaround for this? -- Johann I'm not from the internet, I just work there.
Re: Retroactively adding send and recv functions to a type?
"Johann 'Myrkraverk' Oskarsson" writes: > I meant ALTER TYPE. Adding the send and recv functions doesn't seem > to supported by ALTER TYPE. > Is there a workaround for this? You could manually update the pg_type row, and then if you were being fussy, add pg_depend entries showing the type depends on the functions. regards, tom lane
Retroactively adding send and recv functions to a type?
Dear PostgreSQL General, I wish to add the send and recv functions to a type I didn't write myself. The help for ALTER TABLE seems to indicate this is not supported directly. How can I achieve this? The extension in question is hashtypes on pgxn, https://pgxn.org/dist/hashtypes/ and I did not write it myself, but do want to add support for binary send and recv. I have a 48GB table already populated with sha1, and I'd rather not have to drop it in order to upgrade the extension. Also, if I do this, do I make the extension non-automatically-ugradeable? That is, is there a way to write an upgrade script from a version without send and recv, to a version with it? -- Johann I'm not from the internet, I just work there.
Re: Retroactively adding send and recv functions to a type?
On Tue, Aug 20, 2019 at 1:32 AM Tom Lane wrote: > > "Johann 'Myrkraverk' Oskarsson" writes: > > I meant ALTER TYPE. Adding the send and recv functions doesn't seem > > to supported by ALTER TYPE. > > Is there a workaround for this? > > You could manually update the pg_type row, and then if you were > being fussy, add pg_depend entries showing the type depends on > the functions. Can I do this in a future proof way? That is, is there a way to make that into an upgrade script, or will I make the extension un-upgradable doing that? -- Johann I'm not from the internet, I just work there.
Re: Pgsql resource agent of pacemaker
On Mon, 19 Aug 2019, 18:47 Shital A, wrote: > Hello, > > Need advise on below situation: > > Postgres 9.6 > Pacemaker 1.1.19 > Corosync 2.4.3 > > We are testing HA setup on a two node cluster using pacemaker, corosync > stack. The replication is streaming replication in async mode. > Whenever there is a failover to standby, pgsql created a PGSQL.lock file > in /var/lib/pgsql/tmp/ folder. We noticed that this file is created at > random on any one of the nodes. > > Want to know: > - Is there any logic behind which node it creates the lock file? > > - When a node in kept in HS:alone state ? > > - How can we check if failed node is at a log location behind current > primary? So that we can force RA to start by deleting lock file? > > Please help to understand > > Thanks. > > > > > Any thoughts? Thanks in advance! > >
Re: Retroactively adding send and recv functions to a type?
"Johann 'Myrkraverk' Oskarsson" writes: > On Tue, Aug 20, 2019 at 1:32 AM Tom Lane wrote: >> You could manually update the pg_type row, and then if you were >> being fussy, add pg_depend entries showing the type depends on >> the functions. > Can I do this in a future proof way? That is, is there a way to make > that into an upgrade script, or will I make the extension > un-upgradable doing that? [ shrug... ] Depends what you consider "future proof". I should think that if pg_type.typsend goes away or changes meaning, for example, that would be reflective of changes large enough to break an extension dabbling in binary I/O in other ways anyway. Inserting new rows into pg_depend manually is a bit riskier, but I don't think that catalog has changed since its inception, so it's not all that risky. In any case, you could limit the lifespan of the upgrade script, if you roll it up into a new base install script ASAP. regards, tom lane
Collumn level permissions ?
I do know a way to solve this. I could create a view "B" that is a view of tab;e "A" without column "C" on the select, and give a user permissions on B, but not on A, or at least I think that would work. What I specifically need to do is "hide" one column of a table from a set of users. Any better way to do this? -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
Re: Collumn level permissions ?
On 8/19/19 12:17 PM, stan wrote: I do know a way to solve this. I could create a view "B" that is a view of tab;e "A" without column "C" on the select, and give a user permissions on B, but not on A, or at least I think that would work. What I specifically need to do is "hide" one column of a table from a set of users. Any better way to do this? https://www.postgresql.org/docs/11/sql-grant.html -- Adrian Klaver adrian.kla...@aklaver.com
Can't install postgresql from official postgresql repo on RedHat 8?
Hi Everyone, My apologies first in case this has been posted, but I couldn't find a thread related to my problem. I can't seem to install postgresql from the office PostgreSQL repository? I installed the main repository info, but any attempt to install the server executable or related software, the repository says the info doesn't exist. [root@test04 ~]# dnf install postgresql11 Updating Subscription Management repositories. Last metadata expiration check: 0:14:42 ago on Mon 19 Aug 2019 01:06:11 PM PDT. No match for argument: postgresql11 Error: Unable to find a match Doing a search against all available postgresql installable items shows that none of the appropriate executables are available: [root@test04 ~]# dnf list postgresql* Updating Subscription Management repositories. Last metadata expiration check: 0:14:48 ago on Mon 19 Aug 2019 01:06:11 PM PDT. Available Packages postgresql.x86_64 10.6-1.module+el8+2469+5ecd5aae rhel-8-for-x86_64-appstream-rpms postgresql-contrib.x86_64 10.6-1.module+el8+2469+5ecd5aae rhel-8-for-x86_64-appstream-rpms postgresql-docs.x86_64 10.6-1.module+el8+2469+5ecd5aae rhel-8-for-x86_64-appstream-rpms postgresql-jdbc.noarch 42.2.3-1.el8 rhel-8-for-x86_64-appstream-rpms postgresql-jdbc-javadoc.noarch 42.2.3-1.el8 rhel-8-for-x86_64-appstream-rpms postgresql-odbc.x86_64 10.03.-2.el8 rhel-8-for-x86_64-appstream-rpms postgresql-odbc-tests.x86_64 10.03.-2.el8 rhel-8-for-x86_64-appstream-rpms postgresql-plperl.x86_64 10.6-1.module+el8+2469+5ecd5aae rhel-8-for-x86_64-appstream-rpms postgresql-plpython3.x86_6410.6-1.module+el8+2469+5ecd5aae rhel-8-for-x86_64-appstream-rpms postgresql-pltcl.x86_6410.6-1.module+el8+2469+5ecd5aae rhel-8-for-x86_64-appstream-rpms postgresql-server.x86_64 10.6-1.module+el8+2469+5ecd5aae rhel-8-for-x86_64-appstream-rpms postgresql-server-devel.x86_64 10.6-1.module+el8+2469+5ecd5aae rhel-8-for-x86_64-appstream-rpms postgresql-static.x86_64 10.6-1.module+el8+2469+5ecd5aae rhel-8-for-x86_64-appstream-rpms postgresql-test.x86_64 10.6-1.module+el8+2469+5ecd5aae rhel-8-for-x86_64-appstream-rpms postgresql-test-rpm-macros.x86_64 10.6-1.module+el8+2469+5ecd5aae rhel-8-for-x86_64-appstream-rpms postgresql-unit10.x86_64 7.1-1.rhel8 pgdg10 postgresql-unit10-debuginfo.x86_64 7.1-1.rhel8 pgdg10 postgresql-unit10-debugsource.x86_64 7.1-1.rhel8 pgdg10 postgresql-unit11.x86_64 7.1-1.rhel8 pgdg11 postgresql-unit11-debuginfo.x86_64 7.1-1.rhel8 pgdg11 postgresql-unit11-debugsource.x86_64 7.1-1.rhel8 pgdg11 postgresql-unit96.x86_64 7.1-1.rhel8 pgdg96 postgresql-unit96-debuginfo.x86_64 7.1-1.rhel8 pgdg96 postgresql-unit96-debugsource.x86_64 7.1-1.rhel8 pgdg96 postgresql-upgrade.x86_64 10.6-1.module+el8+2469+5ecd5aae rhel-8-for-x86_64-appstream-rpms postgresql-upgrade-devel.x86_6410.6-1.module+el8+2469+5ecd5aae rhel-8-for-x86_64-appstream-rpms postgresql10-contrib-debuginfo.x86_64 10.10-1PGDG.rhel8 pgdg10 postgresql10-debuginfo.x86_64 10.10-1PGDG.rhel8 pgdg10 postgresql10-debugsource.x86_6410.10-1PGDG.rhel8 pgdg10 postgresql10-devel.x86_64 10.10-1PGDG.rhel8 pgdg10 postgresql10-devel-debuginfo.x86_6410.10-1PGDG.rhel8 pgdg10 postgresql10-libs.x86_64 10.10-1PGDG.rhel8 pgdg10 postgresql10-libs-debuginfo.x86_64 10.10-1PGDG.rhel8 pgdg10 postgresql10-odbc.x86_64 11.00.-1PGDG.rhel8 pgdg10 postgresql10-plperl-debuginfo.x86_64 10.10-1PGDG.rhel8 pgdg10 postgresql10-plpython.x86_64 10.10-1PGDG.rhel8 pgdg10 postgresql10-plpython-debuginfo.x86_64 10.10-1PGDG.rhel8 pgdg10 postgresql10-plpython3-debuginfo.x86_6410.10-1PGDG.rhel8 pgdg10 postgresql10-pltcl-debuginfo.x86_6410.10-1PGDG.rhel8 pgdg10 postgresql10-server-debuginfo.x86_64 10.10-1PGDG.rhel8 pgdg10 postgresql10-tcl.x86_642.4.0-2.rhel8.1 pgdg10 postgresql10-test-debuginfo.x86_64 10.10-1PGDG.rhel8 pgdg10 postgresql11-contrib-debuginfo.x86_64 11.5-1PG
Re: Retroactively adding send and recv functions to a type?
On 19/08/2019 19:32, Tom Lane wrote: > "Johann 'Myrkraverk' Oskarsson" writes: >> I meant ALTER TYPE. Adding the send and recv functions doesn't seem >> to supported by ALTER TYPE. >> Is there a workaround for this? > You could manually update the pg_type row, and then if you were > being fussy, add pg_depend entries showing the type depends on > the functions. We generally discourage updating the catalogs directly. This was why I wrote the CREATE/ALTER DATABASE patch in 2014 that you helped me with (fbb1d7d73f8). I'm willing to work on a patch for ALTER TYPE if it has a chance of being accepted. -- Vik Fearing
SELECT all the rows where id is children of other node.
Hello, I have a huge table (100 million rows) of relations between nodes by id in a Postgresql 11 server. Like this: CREATE TABLE relations ( pid INTEGER NOT NULL, cid INTEGER NOT NULL, ) This table has parent-child relations references between nodes by id. Like: *pid -> cid* n1 -> n2 n1 -> n3 n1 -> n4 n2 -> n21 n2 -> n22 n2 -> n23 n22 -> n221 n22 -> n222 I would like to get a list of all the nodes being children (direct or indirect) of any other node. Example. The children of: 1) n3: [] (n3 has not children) 2) n22: [n221, n222] (n22 has 2 children: n221 and n222) 3) n1: [n2, n21, n22, n23, n221, n222] (n1 has 6 children including indirect children). this pseudo SQL: SELECT * FROM relations WHERE has_parent(myId) It can be solved with a recursive function or stored procedure. But that requires several passes. Is it possible to solve it in one pass? Perhaps using some low-level function or join or some index expression or auxiliary columns? It is OK to create an index or similar using recursive expressions. However, the SELECT expressions should be solved in one pass because of speed. Pablo
Re: SELECT all the rows where id is children of other node.
> On Aug 19, 2019, at 7:42 PM, pabloa98 wrote: > > Hello, > > I have a huge table (100 million rows) of relations between nodes by id in a > Postgresql 11 server. Like this: > > CREATE TABLE relations ( > pid INTEGER NOT NULL, > cid INTEGER NOT NULL, > ) > > This table has parent-child relations references between nodes by id. Like: > > pid -> cid > n1 -> n2 > n1 -> n3 > n1 -> n4 > n2 -> n21 > n2 -> n22 > n2 -> n23 > n22 -> n221 > n22 -> n222 > > I would like to get a list of all the nodes being children (direct or > indirect) of any other node. > > Example. The children of: > > 1) n3: [] (n3 has not children) > 2) n22: [n221, n222] (n22 has 2 children: n221 and n222) > 3) n1: [n2, n21, n22, n23, n221, n222] (n1 has 6 children including indirect > children). > > this pseudo SQL: > > SELECT * > FROM relations > WHERE has_parent(myId) > > It can be solved with a recursive function or stored procedure. But that > requires several passes. Is it possible to solve it in one pass? Perhaps > using some low-level function or join or some index expression or auxiliary > columns? > > It is OK to create an index or similar using recursive expressions. However, > the SELECT expressions should be solved in one pass because of speed. > > > Pablo Are you asking for just the function (always with a seed Id) or the complete transformation in a single select? Would you like the descendants on one line (eg n22,[n221,n222])? I wonder if you might add n3 -> null to explicitly terminate the hierarchy ?
Re: SELECT all the rows where id is children of other node.
> On Aug 19, 2019, at 7:42 PM, pabloa98 wrote:
>
> Hello,
>
> I have a huge table (100 million rows) of relations between nodes by id in a
> Postgresql 11 server. Like this:
>
> CREATE TABLE relations (
> pid INTEGER NOT NULL,
> cid INTEGER NOT NULL,
> )
>
> This table has parent-child relations references between nodes by id. Like:
>
> pid -> cid
> n1 -> n2
> n1 -> n3
> n1 -> n4
> n2 -> n21
> n2 -> n22
> n2 -> n23
> n22 -> n221
> n22 -> n222
>
> I would like to get a list of all the nodes being children (direct or
> indirect) of any other node.
>
> Example. The children of:
>
> 1) n3: [] (n3 has not children)
> 2) n22: [n221, n222] (n22 has 2 children: n221 and n222)
> 3) n1: [n2, n21, n22, n23, n221, n222] (n1 has 6 children including indirect
> children).
>
> this pseudo SQL:
>
> SELECT *
> FROM relations
> WHERE has_parent(myId)
>
> It can be solved with a recursive function or stored procedure. But that
> requires several passes. Is it possible to solve it in one pass? Perhaps
> using some low-level function or join or some index expression or auxiliary
> columns?
>
> It is OK to create an index or similar using recursive expressions. However,
> the SELECT expressions should be solved in one pass because of speed.
>
>
> Pablo
Back at my desk now, to show the possibilities.
with recursive descendants(parent, child) as
(select p.p, p.c from kids p where not exists (select 1 from kids c where c.c =
p.p) group by p.p, p.c
union all
select k.* from kids k, descendants d where k.p = d.child)
select * from descendants;
parent | child
+---
1 | 3
1 | 2
1 | 4
2 |21
2 |22
2 |23
22 | 221
22 | 222
(8 rows)
with recursive descendants(parent, child) as
(select p.p, p.c from kids p where not exists (select 1 from kids c where c.c =
p.p) group by p.p, p.c
union all
select k.* from kids k, descendants d where k.p = d.child)
select d.parent, array_agg(d.child) from descendants d group by d.parent;
parent | array_agg
+
1 | {3,2,4}
22 | {221,222}
2 | {21,22,23}
(3 rows)
Re: postmaster utilization
At Mon, 19 Aug 2019 10:07:30 -0400, Stephen Frost wrote in <20190819140730.gh16...@tamriel.snowman.net> > Greetings, > > * Ron (ronljohnso...@gmail.com) wrote: > > On 8/19/19 5:40 AM, Shiwangini Shishulkar wrote: > > >We have scheduled postgres full backup on centos 7 machine. DB size is > > >around 66 GB. We observed while backup is running, postmaster CPU % > > >reaches to 90 - 100%,which results very strange behavior: that we are not > > >able to perform any DDL and DML on to the database during the time. > > >Please suggest a way to limit it's CPU utilization or else let us know how > > >we can access the database during the backup window. Please let us know on > > >priority since this is our prod database. > > > > Are you compressing the backup? > > How many threads are you running? > > How many CPUs on your system? > > What version of Postgres? > > ... What are you using to run the backup? It seems to have been reported as BUG #15961.. https://www.postgresql.org/message-id/15965-413bf5d18aaef...@postgresql.org > PostgreSQL version: 11.4 > Operating system: CentOS Linux release 7.6.1810 (Core) > pg_dump -U postgres -d wg -f wg.sql regards. -- Kyotaro Horiguchi NTT Open Source Software Center
Re: SELECT all the rows where id is children of other node.
> On Aug 19, 2019, at 7:42 PM, pabloa98 wrote: > > Hello, > > I have a huge table (100 million rows) of relations between nodes by id in a > Postgresql 11 server. Like this: > > CREATE TABLE relations ( > pid INTEGER NOT NULL, > cid INTEGER NOT NULL, > ) > > This table has parent-child relations references between nodes by id. Like: > > pid -> cid > n1 -> n2 > n1 -> n3 > n1 -> n4 > n2 -> n21 > n2 -> n22 > n2 -> n23 > n22 -> n221 > n22 -> n222 > > I would like to get a list of all the nodes being children (direct or > indirect) of any other node. > > Example. The children of: > > 1) n3: [] (n3 has not children) > 2) n22: [n221, n222] (n22 has 2 children: n221 and n222) > 3) n1: [n2, n21, n22, n23, n221, n222] (n1 has 6 children including indirect > children). > > this pseudo SQL: > > SELECT * > FROM relations > WHERE has_parent(myId) > > It can be solved with a recursive function or stored procedure. But that > requires several passes. Is it possible to solve it in one pass? Perhaps > using some low-level function or join or some index expression or auxiliary > columns? > > It is OK to create an index or similar using recursive expressions. However, > the SELECT expressions should be solved in one pass because of speed. > > > Pablo ooops. didn’t get all generations. sorry
