Re: Posgresql 14 and CarbonBlack on RHEL8?
On Tue, Apr 30, 2024 at 10:07 PM Tom Lane wrote: > Ron Johnson writes: > > When running stress tests on the systems (in prod, during the maintenance > > window), 171K events/second are generated on the RHEL8 servers, and CB > > needs (according to top(1)) 325% of CPU to handle that, and still > dropping > > 92% of them. > > The RHEL6 system doesn't bat an eye at running the exact same test (36 > cron > > jobs running psql executing SELECT statements). > > Is JIT enabled on the newer system? If so try turning it off, or else > raise the associated cost settings. We've seen lots of reports of > workloads where, by default, the planner is too aggressive about > applying JIT. > A puzzling suggestion. Why should it impact AV software? At one point, I disabled JIT to test its impact on PG, performance was a bit of a wash (some queries were a bit faster, some were a bit slower), but I didn't monitor CB. Just now, I did ALTER SYSTEM SET jit='off'; and re-ran the stress test. No impact to CarbonBlack.
Table data migration from single server to Flexi server
> Hi team, > > I need help. Recently we migrated our postgreSQL single server to flexi > server. We need to move data from a table from a single server database to > a flexi server database. My table has around 12GB data. We need to migrate > only this table data to the flexi server. > Can someone help me with this. > > Thanks in advance. > > > > -- > Regards > Bagesh kumar singh > mob-802459 > >
Re: Table data migration from single server to Flexi server
Hi Please use pg_dump for taking the backup, you can find the details on the link below. https://www.postgresql.org/docs/current/app-pgdump.html Regards Kashif Zeeshan On Thu, May 2, 2024 at 8:24 AM Bagesh kamar singh wrote: > > Hi team, >> >> I need help. Recently we migrated our postgreSQL single server to flexi >> server. We need to move data from a table from a single server database to >> a flexi server database. My table has around 12GB data. We need to migrate >> only this table data to the flexi server. >> Can someone help me with this. >> >> Thanks in advance. >> >> >> >> -- >> Regards >> Bagesh kumar singh >> mob-802459 >> >>
Re: Linked directory or explicit reference
On 4/30/2024 17:31, Ron Johnson wrote: On Tue, Apr 30, 2024 at 7:00 PM Senor Cervesa wrote: Hi All; When doing an initial install of PostgreSQL on RHEL 7 or 8 derived OS via rpm, what are pros, cons and recommendations of these 2 procedures for utilizing a second disk? Secondary SSD or RAID mounted at /disk2. Option #1 1. install the rpm which creates basic user and home 2. Create symlink /var/lib/pgsql/15/data --> /disk2/data 3. initdb with no special options Or Option #2 1. install the rpm which creates basic user and home 2. initdb with --pgdata=/disk2/data Probably using included 'postgresql-12-setup' script I also link /var/lib/pgsql/data --> ../15/data so automation can reference postgresql.conf without knowing version (legacy stuff). In my experience,The PgBackRest restore feature does not like symlinks. I hadn't considered that and it's the kind of experience feedback I'm looking for. It won't be an issue for me though. The install is automated with a bash script which handles several options including whether there is a second disk for DB. Scripting the install with or without the second disk is straight forward but I'm concerned with either scenario causing unforeseen differences. I don't think there's a benefit to using tablespace here but I have no experience with it. The systemd service is configured with a dependency on the disk mount so I don't think there are different risks for starting postgres with missing data directory. I've run postgres in both scenarios and not had any issues. I'm interested in comments from others on their experience using these or other options. Is the mount point just "/disk2" when using "--pgdata=/disk2/data"? I've gotten "directory not empty" errors when the mount point is "/Database/x.y/data". When linked, it looks like: [root@test110 pgsql]# ll /var/lib/pgsql/15/data lrwxrwxrwx. 1 root root 12 May 1 05:21 /var/lib/pgsql/15/data -> /disk2/data/ I'm not sure what would trigger "directory not empty". When running initdb there is nothing under data. I could see a problem with a symlink throwing that message as a catchall though. I haven't run across any problems yet. Thank you Ron Johnson for the feedback.
Prevent users from executing pg_dump against tables
Hello all, In our production db infrastructure, we have one read_only role which has read privileges against all tables in schema A. We are planning to grant this role to some developers for viewing the data, but also I want to limit the users from executing statements like copy or using pg_dump. Main reason being I don't want the data to be copied from the database to their local machines. I tried by implementing triggers, but was not able to figure out a way to restrict the pg_dump and allow only select statements. Postgresql version - 12 Ec2 based postgres database Is there a way to implement this? Please advise. Thanks & Regards, Rajamohan.J Devops Cloud Architect Email:garajamo...@gmail.com
Re: Prevent users from executing pg_dump against tables
Hi RAJAMOHAN There is not a direct way to restrict a table not to be allowed to be backed up by pg_dump. But you can use the RLS (ROW LEVEL SECURITY) policy to restrict access. Regards Kashif Zeeshan Bitnine Global On Thu, May 2, 2024 at 10:47 AM RAJAMOHAN wrote: > Hello all, > > In our production db infrastructure, we have one read_only role which has > read privileges against all tables in schema A. > > We are planning to grant this role to some developers for viewing the > data, but also I want to limit the users from executing statements like > copy or using pg_dump. Main reason being I don't want the data to be copied > from the database to their local machines. > > I tried by implementing triggers, but was not able to figure out a way to > restrict the pg_dump and allow only select statements. > > Postgresql version - 12 > Ec2 based postgres database > > Is there a way to implement this? Please advise. > > > Thanks & Regards, > Rajamohan.J > Devops Cloud Architect > Email:garajamo...@gmail.com >
