Command line session and running queries
Hello,I've run an update query inside command line tool (psql) then I've exit the tool (closing the window). When I re-logged in, I've noticed in pg_stat_activity that my query was still running.I used the command pg_cancel_backend with the right PID, but it returned "f" few seconds later.I re-checked pg_stat_activity and I was not able to see my query; there was only an autovacuum running on the table impacted by my query.Since my update query usually takes about one hour, I was wandering what happened. Is postgres regularly checking command line session? If session has expired, would postgres stop the query and rollback it? I'm using both client and server v10.1. Thanks in advance.
Re: upgrading from pg 9.3 to 10
Hi Stephen, thanks for the feedback, it is much appreciated! On Mon, Aug 20, 2018 at 12:15 PM Stephen Frost wrote: > * bricklen (brick...@gmail.com) wrote: > > Using pg_upgrade, it is definitely possible to upgrade 9.3 to 10 in one > > jump. We did this in production earlier this year for 1500 Postgres > > clusters. > > At https://bricklen.github.io/2018-03-27-Postgres-10-upgrade/ I > documented > > (in excrutiating detail, perhaps) how we upgraded those clusters from 9.3 > > to 10 with sub-15 minute downtime per cluster. The only real issues we > ran > > into were some corrupted indexes that appeared to be related to 10.1 and > > 10.2. Upgrading to 10.3/10.4 and reindexing fixed those particular > > corrupted indexes. > > Interesting write-up. A few points: > > #1: I'd strongly recommend including something in the write-up about > checking for unlogged tables. Those can add quite a bit of time to the > rsync if they aren't truncated before the primary cluster is shut down. > A note about UNLOGGED has been added to the page, thanks. > #2: The issue with timelines leads me to suspect that you had a > restore_command defined and that when PG started up, it found the > timeline history files from the old cluster. If you don't have a > restore_command set for any of these then I'm very curious what > happened. The other possibility (though I wouldn't have expected a > timeline complaint from PG...) is that the replica wasn't fully up to > date for whatever reason. > Entirely plausible, unfortunately I don't recall what all the settings were in the replicas, nor what the actual errors were. One puzzling aspect was that every one of those 1500 clusters had the same primary and replica settings, and every one of them had been failed back and forth dozens of times before we upgraded to PG10. The replica not being fully up to date makes more sense than rsync copying over history files only part of the time, since we were less focused on the remote replica than ensuring the primary was successfully upgraded. > #3: There's a number of checks discussed in the upgrade documentation > around using the rsync-based method, but it doesn't seem like you did > those. A mention of them might be good. If you mean #8 from the docs, where it says "If you are upgrading standby servers using methods outlined in section Step 10, verify that the old standby servers are caught up by running pg_controldata against the old primary and standby clusters. Verify that the “Latest checkpoint location” values match in all clusters. (There will be a mismatch if old standby servers were shut down before the old primary.) ", we found that the pg_controldata output from the hot standby never matched the output from the primary. When I was investigating at the time, I read a note somewhere that if the replica was not using Streaming Replication the pg_controldata output was unlikely to match. It's possible I misunderstood (or I've forgotten what I read), but the output did not match in the dozen-ish times we tested that particular aspect. It's entirely possible it was due to me bungling something, but I was careful to checkpoint + switch wal files + shut down the primary cleanly before checking that all the WALs from the primary were shipped to the hot standby and were applied successfully, before gracefully shutting down the hot standby. In all that testing, the systems were quiescent, as we'd already blocked access to the primary. If you meant other checks, I'd be happy to add a note about them if you can point out which ones they were. It's possible we overlooked something in our planning and execution of the pg_upgrade steps. > Note that these are > particularly important because the rsync will *not* copy over changes to > the data files except in the relatively rare case of the relfilenode's > size changing (given that most of them are 1G, that's not too likely). > The note you have about the rsync taking more time due to "if the > remote replica was fully caught up when it was shut down" isn't > accurate- there is no WAL replay that happens on the replica using this > method to 'catch up' and if WAL replay was required to have this process > be correct then it simply wouldn't ever work. > No doubt the problem exists between my ears and I'm making invalid assumptions about the problems we ran into. What I meant say -and please correct me if this still inaccurate- was that once the primary was stopped cleanly, all WALs generated needed to be shipped to the remote replica, where those WALs needed to be applied to bring the replica to a point where it is near-identical the primary. At that point, the rsync would only ship the changes created by the pg_upgrade process itself. The point about being "caught up" was the if the replica hadn't applied all the WALs from the primary, it would have more data files different to the primary, thus resulting in more data to rsync. I'm happy to be corrected if that's wrong. > #4:
Re: Can Pg somehow recognize/honor linux groups to control user access ?
On 08/22/2018 05:17 PM, Tim Cross wrote: > > I hope your not suggesting that multiple users use the same login > credentials to access the database? A database role is effectively a "shared account" with "shared password" so I'm not sure what your point is. It's just not a "computer login" shared account. That said, there is nothing scary about shared computer accounts either. It's the users that are the scary part. -- Dimitri Maziuk Programmer/sysadmin BioMagResBank, UW-Madison -- http://www.bmrb.wisc.edu signature.asc Description: OpenPGP digital signature
Re: Can Pg somehow recognize/honor linux groups to control user access ?
Dimitri Maziuk writes: > On 08/22/2018 11:38 AM, Ravi Krishna wrote: >> >> In fact DBAs don't even need to get involved when a new user needs DB >> access. Sysadmin had to just add that user in a group and we are done. > > How is that different from giving your grants to a database role and > just telling the new user the name and password of that role to connect as? I hope your not suggesting that multiple users use the same login credentials to access the database? This would totally destroy the important security principals of attribution and repudiation. I have not looked at PAM for PG, but that is certainly something worth checking out. I have used it in other contexts and it is very powerful. Depending on the implementation, theoretically, it should be possible to have PAM provide the information to determine what database roles to give to a login. What you will probably need is some additional component to act as the 'meta-directory' so that you can have consistent usernames, uid/gid across servers/systems (i.e. ldap/ad) Something else to look at is openLDAP. Again, not yet looked at it in the PG context, but have used it successfully to manage access to other systems (including databases in other RDMS) before. Currently, we use openLDAP and PAM to manage access on Linux servers. One of the things on my 'todo' list is to look at it in a PG context, just haven't got there yet. Avoid any solution which requires a 'generic' account with shared passwords. Apart from the security implications, you will almost certainly run into problems with auditors and many regulatory standards. -- Tim Cross
Re: Can Pg somehow recognize/honor linux groups to control user access ?
On 08/22/2018 11:38 AM, Ravi Krishna wrote: > > In fact DBAs don't even need to get involved when a new user needs DB access. > Sysadmin had to just add that user in a group and we are done. How is that different from giving your grants to a database role and just telling the new user the name and password of that role to connect as? -- Dimitri Maziuk Programmer/sysadmin BioMagResBank, UW-Madison -- http://www.bmrb.wisc.edu signature.asc Description: OpenPGP digital signature
Re: Can Pg somehow recognize/honor linux groups to control user access ?
On Wed, Aug 22, 2018 at 9:38 AM, Ravi Krishna wrote: > I am not sure this is the same what I was thinking about. For example, in > db2, connect privilege can be granted to a group and so is grant privilege > as in > GRANT SELECT ON TABLE SCHEMA.TABLE TO GROUP ABC; > GRANT CONNECT ON DATABASE TO GROUP ABC > > The "LOGIN" attribute for a Role is not inheritable and I do not imagine changing it to be is something the project can consider. Every login user must have their own role in the database. These roles are separate from O/S users though "mappings" can be established between the two in pg_hba.conf. David J.
Re: Can Pg somehow recognize/honor linux groups to control user access ?
On 08/22/2018 08:56 AM, David Gauthier wrote: Hi: The title says it all. I need to be control who can gain access to a DB based on a linux user group. I can set up a generic role and password, but also want to prevent users who are not in a specific linux group from accessing the DB. For code that works with the DB, this is easy (just chmod the group on the code file(s)). But is there a way to add an additional gauntlet that checks membership in the linux group if, for example, they were trying to get in using psql at the linux prompt ? There are a couple hundred in the linux group and the list of names changes constantly. I suppose creating a DB role per user in the linux group may be possible if something like a cron was maintaining this (creating/dropping uid based roles as the group membership changes) then give everyone the same password. But does that prevent someone outside the linux group from just logging in with someone else's uid and the generic password? I'm hoping that this is a common need and that someone has a good solution. Thanks in Advance for any help! You could probably write a pam module to do it but it seems to be your are inverting the problem and should be looking at this from a Postgres not Linux perspective. Perhaps consider using an SSO solution for both Linux and Postgres. JD -- Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc *** A fault and talent of mine is to tell it exactly how it is. *** PostgreSQL centered full stack support, consulting and development. Advocate: @amplifypostgres || Learn: https://postgresconf.org * Unless otherwise stated, opinions are my own. *
Re: Can Pg somehow recognize/honor linux groups to control user access ?
AFAIK PG does not support it , as yet. IMO this should be implemented as a priority.
Can Pg somehow recognize/honor linux groups to control user access ?
Hi: The title says it all. I need to be control who can gain access to a DB based on a linux user group. I can set up a generic role and password, but also want to prevent users who are not in a specific linux group from accessing the DB. For code that works with the DB, this is easy (just chmod the group on the code file(s)). But is there a way to add an additional gauntlet that checks membership in the linux group if, for example, they were trying to get in using psql at the linux prompt ? There are a couple hundred in the linux group and the list of names changes constantly. I suppose creating a DB role per user in the linux group may be possible if something like a cron was maintaining this (creating/dropping uid based roles as the group membership changes) then give everyone the same password. But does that prevent someone outside the linux group from just logging in with someone else's uid and the generic password? I'm hoping that this is a common need and that someone has a good solution. Thanks in Advance for any help!
Re: "checkpointer process" is consuming more memory. How to control it?
22.08.2018 16:43, Raghavendra Rao J S V wrote: We have a database cluster as "db1_data". Under this cluster we have two databases. one is *db1 *and other is *qovr*. I surprised to see as "checkpointer process" is consuming 8.73GB of memory(RSS value as 9158892). Why "checkpointer process" is consuming this much amount of memory and how to limit the usage of the "checkpointer process" memory. RSS value is not reasonable to determine memory leaks because it takes into account shared segments (e.g. from shared buffer cache). As a long-lived process checkpointer process tries to flush and as a consequence to touch each buffer cell therefore its RSS approaches to local allocated memory plus shared_buffers. If you want to know the real local memory consumption you may to use python utility *smem* to see unshared local memory size. -- Regards, Maksim Milyutin
Re: "checkpointer process" is consuming more memory. How to control it?
On 08/22/2018 06:43 AM, Raghavendra Rao J S V wrote: Hi All, We have a database cluster as "db1_data". Under this cluster we have two databases. one is *db1 *and other is *qovr*. I surprised to see as "checkpointer process" is consuming 8.73GB of memory(RSS value as 9158892). Why "checkpointer process" is consuming this much amount of memory and how to limit the usage of the "checkpointer process" memory. Take a look at: https://www.postgresql.org/docs/10/static/wal-configuration.html image.png -- Regards, Raghavendra Rao J S V -- Adrian Klaver adrian.kla...@aklaver.com
Re: Hungarian collation in English Windows server
Durumdara wrote: > CREATE DATABASE yyy > WITH > OWNER = xxx > ENCODING = 'UTF8' > LC_COLLATE = 'Hungarian_Hungary.1250' < > LC_CTYPE = 'Hungarian_Hungary.1250' < > > But we got error that collation doesn't exists in English Windows server. You have to install the collation on the Windows maching, not in PostgreSQL. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Hungarian collation in English Windows server
Dear Members! One of the customer preinstalled a 9.6 PGSQL server for us. Today we tried to create and migrate our database. CREATE DATABASE yyy WITH OWNER = xxx ENCODING = 'UTF8' *LC_COLLATE = 'Hungarian_Hungary.1250' <* *LC_CTYPE = 'Hungarian_Hungary.1250' <* But we got error that collation doesn't exists in English Windows server. How to we add this collation in english server? What we can do now? This is a problematic/unpleasant situation because they wait for us, but we didn't use only Hungarian servers where this collation is default. Thank you for your help! Regards dd
Re: Linker errors while creating a PostgreSQL C extension function.
TalGloz wrote: > Do you or anyone know why is it trying to link with -L/usr/lib64 path and > not -L/usr/local/lib as provided? > > After recompiling the libseal.a with the -fPIC flag and copying it manually > from /usr/local/lib/ to /usr/lib64/ I get those errors: > > g++ -Wl,--no-undefined -shared -o seal_diff_cpp.so seal_diff_cpp.o > -L/usr/pgsql-10/lib -L/usr/lib64 -Wl,--as-needed > -Wl,-rpath,'/usr/pgsql-10/lib',--enable-new-dtags -L/usr/local/lib -lseal > -pthread > seal_diff_cpp.o: In function `seal_diff_cpp': > /etc/opt/pgsql_c_functions/CPP/seal_extension/seal_diff_cpp.cpp:106: > undefined reference to `pg_detoast_datum_packed' > /etc/opt/pgsql_c_functions/CPP/seal_extension/seal_diff_cpp.cpp:107: > undefined reference to `pg_detoast_datum_packed' > /etc/opt/pgsql_c_functions/CPP/seal_extension/seal_diff_cpp.cpp:108: > undefined reference to `pg_detoast_datum_packed' > /etc/opt/pgsql_c_functions/CPP/seal_extension/seal_diff_cpp.cpp:112: > undefined reference to `text_to_cstring' > /etc/opt/pgsql_c_functions/CPP/seal_extension/seal_diff_cpp.cpp:113: > undefined reference to `text_to_cstring' > /etc/opt/pgsql_c_functions/CPP/seal_extension/seal_diff_cpp.cpp:114: > undefined reference to `text_to_cstring' > /etc/opt/pgsql_c_functions/CPP/seal_extension/seal_diff_cpp.cpp:130: > undefined reference to `cstring_to_text_with_len' > > Did I miss something in one of my files? I think the --no-undefined is wrong. Any reference to PostgreSQL functions is undefined at build time and gets resolved when the shared library is loaded into PostgreSQL. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
