Postgres DB hacked.
Dear All, Thanks for your support. We faced the below issue and our all DB backup data has lost. It seems Hacker deleted all the tables in the DB and created a single table called "warning". when I am trying to select this table so it's showing Error (relation "warning" does not exist). Hacker created one more database "please_read_me_xmg" with single 'warning' table. Could you please let us know how we can secure postgres DB from hackers.Although we have specified only one IP in pg_hba.conf file instead of all.
Re: v11.5- v15.3 upgrade (linux)
Greetings, On 3/6/24 19:19, David Gauthier wrote: Hi: I'm a PG user in a big corp with an IT dept that administers a PG server/instance that I use. It's an old install, v11.5, and we need to upgrade to v15.3. They want to bring the upgraded DB up on a new linux vm which has OS upgrades of its own. So it's a move AND an upgrade. There are 2 concerns First has to do with a jump from 11.5 - 15.3 ? Is it safe to do this given so many major intermediate versions being skipped ? Generally speaking, it is safe from database point of view but you have to verify that application is working as expected with PostgreSQL 15, driver update, any query performance issues, any deprecate features in use, collation differences, and performance verification, etc. PostgreSQL supports dump/restore (slow and longer downtime), binary upgrade using pg_upgrade (faster and low downtime), and logical replication (complex and least downtime). Since OS upgrade is also part of the equation, dump/restore or logical are better candidates. Due to OS collation difference I would avoid binary upgrade path. -- Kind Regards, Yogesh Sharma PostgreSQL, Linux, and Networking Expert Open Source Enthusiast and Advocate PostgreSQL Contributors Team @ RDS Open Source Databases Amazon Web Services: https://aws.amazon.com
Size of pg_multixact/members increases 11355
Dear All, I am using PostgreSQL 9.3.6 version and PGDATA pg_multixact.members folder size is increased to around 3GB. How to reduce this folder size and how to fix this issue? Is it realted to poatgres issue? If yes how to reproduce this issue? Thanks in advance. Regards, Yogesh
Re: Size of pg_multixact/members increases 11355
Please share direction to resolve below issue. Regards, Yogesh On Tuesday, December 12, 2017, Yogesh Sharma wrote: > Dear All, > > I am using PostgreSQL 9.3.6 version and PGDATA pg_multixact.members folder > size is increased to around 3GB. How to reduce this folder size and how to > fix this issue? > Is it realted to poatgres issue? If yes how to reproduce this issue? > > Thanks in advance. > Regards, > Yogesh >
Re: Size of pg_multixact/members increases 11355
Dear Thomas and All, Thanks for sharing your input. How we calculate autovacuum_multixact_freeze_max_age paramter limit into system? Regards, Yogesh On Wednesday, December 13, 2017, Thomas Munro wrote: > On Wed, Dec 13, 2017 at 5:05 AM, David G. Johnston > wrote: > > On Tue, Dec 12, 2017 at 2:52 AM, Yogesh Sharma > > wrote: > >> > >> Dear All, > >> > >> I am using PostgreSQL 9.3.6 version and PGDATA pg_multixact.members > folder > >> size is increased to around 3GB. How to reduce this folder size and > how to > >> fix this issue? > >> Is it realted to poatgres issue? If yes how to reproduce this issue? > >> > > > > I'm not familiar enough with this aspect of PostgreSQL but unless you > > upgrade and find this is still broken in 9.3.20 the willingness and > ability > > to help is going to be impacted. > > Between .6 and .20 there were indeed many bugfixes relating to multixacts: > > https://www.postgresql.org/docs/9.3/static/release-9-3-7.html > https://www.postgresql.org/docs/9.3/static/release-9-3-9.html > https://www.postgresql.org/docs/9.3/static/release-9-3-10.html > https://www.postgresql.org/docs/9.3/static/release-9-3-14.html > https://www.postgresql.org/docs/9.3/static/release-9-3-18.html > > If your subject line means that you have "only" 11355 files under > pg_multixact/member then I doubt you've hit any of the problems > covered by those bugs, but still, yeah, you should upgrade. > > The number of 256kb files under pg_multixact/offsets should normally > be limited by autovacuum_multixact_freeze_max_age, where each file > holds 65536 multixacts. The number of 256kb files under > pg_multixact/members is more complicated: it depends on the number of > members in each multixact, which could be as low as 2, or much higher, > depending how many shared lockers you tend to have. Each file holds > 52352 members. The manual says we default to a "relatively low 400 > million multixacts", but that's actually enough to eat a lot of disk > space, especially if you have large multixacts. 400 million > multixacts = 6103 files = ~1.5GB of pg_multixact/offsets, and clearly > the member directory will be even bigger. If you want to use less > disk space, you should set autovacuum_multixact_freeze_max_age lower. > You don't want to set it too low though, or your system will spend too > much time doing freeze vacuums trying to garbage collect multixacts. > You can work out the peak size of pg_multixact/offsets for a given > autovacuum_multixact_freeze_max_age setting as I showed, and you can > assume that your current ratio of offsets:members size will be > maintained to estimate the peak member space usage. > > 9.3 is also prone to eating more multixact space than recent major > versions in cases involving subtransactions. > > -- > Thomas Munro > http://www.enterprisedb.com >
Re: Size of pg_multixact/members increases 11355
Dear Thomas , Thanks for sharing information. Is it possible to remove mentioned folder files in some time intervals by some DB command? Currently i can not upgrade to 3.6.20. So please share if any solution is available. Regards, Yogesh On Wednesday, December 13, 2017, Yogesh Sharma wrote: > Dear Thomas and All, > > > Thanks for sharing your input. > How we calculate > autovacuum_multixact_freeze_max_age paramter limit into system? > > Regards, > Yogesh > On Wednesday, December 13, 2017, Thomas Munro < > thomas.mu...@enterprisedb.com> wrote: > >> On Wed, Dec 13, 2017 at 5:05 AM, David G. Johnston >> wrote: >> > On Tue, Dec 12, 2017 at 2:52 AM, Yogesh Sharma >> > wrote: >> >> >> >> Dear All, >> >> >> >> I am using PostgreSQL 9.3.6 version and PGDATA pg_multixact.members >> folder >> >> size is increased to around 3GB. How to reduce this folder size and >> how to >> >> fix this issue? >> >> Is it realted to poatgres issue? If yes how to reproduce this issue? >> >> >> > >> > I'm not familiar enough with this aspect of PostgreSQL but unless you >> > upgrade and find this is still broken in 9.3.20 the willingness and >> ability >> > to help is going to be impacted. >> >> Between .6 and .20 there were indeed many bugfixes relating to multixacts: >> >> https://www.postgresql.org/docs/9.3/static/release-9-3-7.html >> https://www.postgresql.org/docs/9.3/static/release-9-3-9.html >> https://www.postgresql.org/docs/9.3/static/release-9-3-10.html >> https://www.postgresql.org/docs/9.3/static/release-9-3-14.html >> https://www.postgresql.org/docs/9.3/static/release-9-3-18.html >> >> If your subject line means that you have "only" 11355 files under >> pg_multixact/member then I doubt you've hit any of the problems >> covered by those bugs, but still, yeah, you should upgrade. >> >> The number of 256kb files under pg_multixact/offsets should normally >> be limited by autovacuum_multixact_freeze_max_age, where each file >> holds 65536 multixacts. The number of 256kb files under >> pg_multixact/members is more complicated: it depends on the number of >> members in each multixact, which could be as low as 2, or much higher, >> depending how many shared lockers you tend to have. Each file holds >> 52352 members. The manual says we default to a "relatively low 400 >> million multixacts", but that's actually enough to eat a lot of disk >> space, especially if you have large multixacts. 400 million >> multixacts = 6103 files = ~1.5GB of pg_multixact/offsets, and clearly >> the member directory will be even bigger. If you want to use less >> disk space, you should set autovacuum_multixact_freeze_max_age lower. >> You don't want to set it too low though, or your system will spend too >> much time doing freeze vacuums trying to garbage collect multixacts. >> You can work out the peak size of pg_multixact/offsets for a given >> autovacuum_multixact_freeze_max_age setting as I showed, and you can >> assume that your current ratio of offsets:members size will be >> maintained to estimate the peak member space usage. >> >> 9.3 is also prone to eating more multixact space than recent major >> versions in cases involving subtransactions. >> >> -- >> Thomas Munro >> http://www.enterprisedb.com >> >
Re: Size of pg_multixact/members increases 11355
Dear Thomas, Thanks for your updation. I have also checked on older version 8.1.3. In older version , after 32 MB folder size increased, it automatically cleaned files and size is reduced. This size is dependent on which paramter in older version? Regards, Yogesh On Wednesday, December 13, 2017, Tomas Vondra wrote: > > > On 12/13/2017 01:42 PM, Yogesh Sharma wrote: > > Dear Thomas , > > > > Thanks for sharing information. > > > > Is it possible to remove mentioned folder files in some time intervals > > by some DB command? > > Currently i can not upgrade to 3.6.20. > > So please share if any solution is available. > > > > There are no explicit commands to remove them. When the database decides > those files are not needed, it'll remove them automatically. > > It's strongly discouraged to mess with those files directly, as it may > easily lead to data loss or data corruption, particularly when it's > unclear why the directory got so large. > > You could try setting autovacuum_multixact_freeze_max_age=0 and > vacuuming the database, but it's hard to say if that will help. > > regards > > -- > Tomas Vondra http://www.2ndQuadrant.com > PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services >
Re: Size of pg_multixact/members increases 11355
Dear All, I have upgraded version from 9.3.6 to 9.3.20 but this issue is not resolved. can you please suggest in which version this file size are not increased. Regards, Yogesh On Thursday, December 14, 2017, Alvaro Herrera wrote: > Yogesh Sharma wrote: > > Dear Thomas , > > > > Thanks for sharing information. > > > > Is it possible to remove mentioned folder files in some time intervals by > > some DB command? > > Currently i can not upgrade to 3.6.20. > > So please share if any solution is available. > > If you do not upgrade, you risk getting your data corrupted. The > solution is to upgrade, and if the data has any value, the suggestion is > to upgrade urgently. > > -- > Álvaro Herrerahttps://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services >
Regarding pg_multixact/members folder size cleanup in postgres 9.3.6.
Dear All, Thanks for your support and guidance. I am using postgres 9.3.6 version and i am using multiple INSERT/UPDATE SQL commands with explicit share lock. I know pg_multixact/members folder contains transactions of share lock. But these files are continuously increases. I have checked PostgreSQL conf file and by default parameters are used in my system. When i have checked same behaviour in older versions of PostgreSQL, this folder size was not increased and continuously cleanup in older versions. Some one please let me know below points. It will really helpful to understand postgres behavior. 1. When this folder pg_multixact/members folder size will cleanup? 2. How can i identify that at that time it will start cleanup? 3. Does it depends on some postgres config parameters? What is config parameters and what will be value of that parameter? 4. I Want older postgres behavior in newer versions. So how to set this behavior through postgres parameters? Thanks in advance. Regards, Yogesh
Re: Regarding pg_multixact/members folder size cleanup in postgres 9.3.6.
Dear David, Thanks for update. I have also checked in postgres 9.3.21 and 9.5.x version. But this is same as 9.3.6 postres version. Regards, Yogesh On Wednesday, February 14, 2018, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Wed, Feb 14, 2018 at 9:06 AM, Yogesh Sharma > wrote: > >> I am using postgres 9.3.6 version and i am using multiple INSERT/UPDATE >> SQL commands with explicit share lock. >> > [...] > > >> 4. I Want older postgres behavior in newer versions. So how to set this >> behavior through postgres parameters? >> > > You need to upgrade to 9.3.21. Also, given the issues surrounding early > 9.3 releases, did this cluster/database begin its life at 9.3.6 or was > there an upgrade path to get there? > > I don't know whether this is configurable but I would suspect not - your > likely issue is software bugs that have since been fixed in one or more of > the 15 releases since 9.3.6 > > David J. > >
Re: Regarding pg_multixact/members folder size cleanup in postgres 9.3.6.
Dear David, > > > Thanks for update. > I have also checked in postgres 9.3.21 and 9.5.x version. But this is same > as 9.3.6 postres version. > Some one please let me know below points. It will really helpful to > understand postgres behavior. > 1. When this folder pg_multixact/members folder size will cleanup? > 2. How can i identify that at that time it will start cleanup? > 3. Does it depends on some postgres config parameters? What is config > parameters and what will be value of that parameter? > 4. I Want older postgres behavior in newer versions. So how to set this > behavior through postgres parameters? > > > Regards, > Yogesh > > On Wednesday, February 14, 2018, David G. Johnston < > david.g.johns...@gmail.com> wrote: > >> On Wed, Feb 14, 2018 at 9:06 AM, Yogesh Sharma >> wrote: >> >>> I am using postgres 9.3.6 version and i am using multiple INSERT/UPDATE >>> SQL commands with explicit share lock. >>> >> [...] >> >> >>> 4. I Want older postgres behavior in newer versions. So how to set this >>> behavior through postgres parameters? >>> >> >> You need to upgrade to 9.3.21. Also, given the issues surrounding early >> 9.3 releases, did this cluster/database begin its life at 9.3.6 or was >> there an upgrade path to get there? >> >> I don't know whether this is configurable but I would suspect not - your >> likely issue is software bugs that have since been fixed in one or more of >> the 15 releases since 9.3.6 >> >> David J. >> >>
