Re: Postgresql & PGPool packages minor version different on Standby server
Hi Vikas, For the postgres service, we have the same case (each cluster members has different minor version of 9.6) and there is no problem with that. But be careful about extensions. Because sometimes an extension can require an upgrade in its db structure otherwise it won't work. So if there is a case like what I describe, you must upgrade the extension before put the server in production. I don't know about pgpool. Regards, İbrahim. On Wed, 18 Jul 2018, 17:42 Vikas Sharma, wrote: > Hi All, > > We have Postgresql 9.5 Cluster with streaming replication and pgpool. The > version of Postgres is 9.5.5 and Pgpool-II version 3.2.15. > > There is now hardware issue with the Standby Machine and it won't startup > so we are building new Standby machine. > > My question is about the minor version of postgresql 9.5 available now. On > postgresql repository site, I can't see postgres 9.5.5 and Pgpool-II-3.2.15 > instead there are 9.5.13 and PGpool-II-3.5.15 > > Is it safe and fine to have postgres 9.5.5 and pgpool-II-3.2.15 on Master > & postgres 9.5.13 and Pgpool-II-3.5.15 on Standby ? > Please let me know. > > I know the postgres don't keep minor version in PG_VERSION file in data > directory so should It be ok to use different minor versions on master & > standby? > > Thanks in advance. > > Regards > Vikas >
FATAL: invalid page in block 0 of relation global/1262
Hi, I upgraded my cluster from 9.6 to 11 with pg_upgrade hardlink parameter two days ago. Since then it has been working great as v11. Today while running heavy update queries on it, I got the "FATAL: invalid page in block 0 of relation global/1262" message and server crushed. Hard reset and trying to start, it start but shows the same error message and do not let me to connect. "psql: FATAL: invalid page in block 0 of relation global/1262". I read that the filenode 1262 is for pg_database table. Any idea why did it happen and can it be recoverable? Thanks. Regards.
invalid memory alloc request size 576460752438159360
Hi, We are getting same error a lot for more than 1 days from different schemas in the same db. < user=myuser db=mydb host=mydbip pid=18883 app=[unknown] time=2017-12-31 14:28:16.056 +03 > ERROR: invalid memory alloc request size 576460752438159360 CentOS Linux release 7.4.1708 (Core) DB version: 9.6.6 Memory: 256GB CPU 2.2 Ghz 24 core Disk: SAN Multipath * write_cache is disabled * there is no incorrect work_mem parameter setting. * logical dump is working, (maybe) no curruption in data. * there is streaming replication, we do not repeat the error in the replicas. (replicas in different minor versions, 9.6.4, 9.6.3 accordingly) * we have large_object field, logical_dump also works with large_objects fields. Any idea?
Re: invalid memory alloc request size 576460752438159360
Hi Peter, I just installed and used amcheck_next, I have used your sample query on the git page (changed the schema name) and that listed all indexes different schemes and produced same outputs like yours with bt_index_check field as empty, that means no error. Am I doing right? 2017-12-31 16:58 GMT+03:00 Peter Geoghegan : > On Sun, Dec 31, 2017 at 1:50 PM, Ibrahim Edib Kokdemir > wrote:> * write_cache is disabled > > * there is no incorrect work_mem parameter setting. > > * logical dump is working, (maybe) no curruption in data. > > * there is streaming replication, we do not repeat the error in the > > replicas. (replicas in different minor versions, 9.6.4, 9.6.3 > accordingly) > > * we have large_object field, logical_dump also works with large_objects > > fields. > > > > Any idea? > > This is very likely to be corruption. It's important to determine the > cause and extent of this corruption. I suggest using amcheck for this, > which is available for those Postgres versions from: > > https://github.com/petergeoghegan/amcheck > > Note that there are Debian and Redhat packages available. > > You'll definitely want to use the "heapallindexed" option here, at > least for primary key indexes (pass "pg_index.indisprimary" as > "heapallindexed" argument, while generalizing from the example SQL > query for bt_index_check()). This process has a good chance of > isolating the problem, especially if you let this list see any errors > raised by the tool. > > -- > Peter Geoghegan >
Re: invalid memory alloc request size 576460752438159360
> > > As I mentioned earlier, if this takes too long, you could only do > > heapallindexed checking once per table (not once per index) by giving > > "indisprimary" as the heapallindexed argument. That way, only primary > > keys would be verified against the heap, which is potentially a lot > > faster. > > Oh, and I think that you should remove the "ORDER BY c.relpages DESC > LIMIT 10", too. My db is not big enough right now. Queries did not take too long. I definitely did all the things you said. And I got only long lists without errors. That is actually a good thing for me. Thanks a lot.
Re: invalid memory alloc request size 576460752438159360
hi Peter, today, we took the dump of database and restored to another empty cluster and run the queries on it for test purposes, no problem at all. All errors are gone.
best way to storing logs
Hi, In our environment, we are logging "all" statements because of the security considerations (future auditing if necessary). But the system is very big and produces 100GB logs for an hour and we expect that this will be much more. We are having trouble to find the disk for this amount of data. Now, we are considering one of the following paths: - deduped file system for all logs. - parsing useful lines with syslog server or pgaudit. And are there any drawbacks for using remote syslog for the logs? What should be the right path for that? Regards, Ibrahim.
Re: Streaming replication: replicant server not starting (9.4.4, Win 2008)
Hi Tim, You have to enable hot_standby=on parameter on the replica server. See the below link. https://wiki.postgresql.org/wiki/Hot_Standby Regards, Ibrahim. 2018-01-30 21:16 GMT+03:00 Tim Bowden : > I've inherited a PG 9.4.4 install on Win 2008 that I'm wanting to > stream from (abt 80Gb on disk). Everything seems to be working from > the "master" side. > > I have an AMI of the master host (a vmware instance in our soon to > close data centre) that has been spun up in AWS (replicant). In it's > "default" configuration (ie, exactly the same as master) it worked > fine. > > I ran pg_basebackup on the master (from memory- details on work pc): > pg_basebackup -D "e:\\mybasebackup\\" -F t -R -X f -z -c fast \ > -h -U > > Base backup is created fine. I've copied it up to AWS and extracted it > into the correct location (after ensuring it is empty) on the replicant > windows host (tar -xzvf base.tgz from a linux box with the pg data > drive mounted). File metadata is changed during the extraction (ie, > file timestamps- could this possibly be an issue?) but permissions on > the win2008 replicant host have been set & double checked. > > When I try to start the pg server on replicant, the log streaming from > master works (and keeps working till the server is rebooted), but the > server can't start for some reason. > > Copy of pg log file on replicant: > > 2018-01-30 17:18:02 AWST LOG: database system was shut down in > recovery at 2018-01-30 17:16:59 AWST > 2018-01-30 17:18:02 AWST LOG: entering standby mode > 2018-01-30 17:18:02 AWST LOG: redo starts at C26/84018A30 > 2018-01-30 17:18:02 AWST LOG: consistent recovery state reached at > C26/8401C6D0 > 2018-01-30 17:18:02 AWST LOG: invalid record length at C26/8401C6D0 > 2018-01-30 17:18:03 AWST LOG: started streaming WAL from primary at > C26/8400 on timeline 1 > 2018-01-30 17:18:03 AWST FATAL: the database system is starting up > 2018-01-30 17:18:04 AWST FATAL: the database system is starting up > 2018-01-30 17:18:05 AWST FATAL: the database system is starting up > 2018-01-30 17:18:06 AWST FATAL: the database system is starting up > 2018-01-30 17:18:07 AWST FATAL: the database system is starting up > 2018-01-30 17:18:08 AWST FATAL: the database system is starting up > > The last line is repeated for about a minute till it gives up. It > leaves behind a bunch of processes that keep streaming log files as > master creates new wal records. This keeps going till the replicant > box is rebooted (easiest way to clean up the processes and free up port > 5432). > > I can keep rebooting the replicant host and on retrying to start the pg > server, it will apply any new wal files that have been streamed, then > fail to start the server but keep streaming again. > > The Windows service start command for PG: > > "C:\Program Files (x86)\PostgreSQL\9.4.4\bin\pg_ctl.exe" runservice -N > "postgresql-9.4" -D "E:\PostgreSQL\9.4.4\data" -w > > Config files: > > Master postgresql.conf: > > dynamic_shared_memory_type = windows > wal_level = hot_standby # Was "archive", but we'd like to do > hot... > archive_mode = on > archive_command = 'copy "%p" > "e:\\PostgreSQL\\9.4.4\\wal_archive\\%f"' # Windows > max_wal_senders = 8 > wal_keep_segments = 900 # Sick of losing old segments while I fix this > max_replication_slots = 8 > log_line_prefix = '%t ' > log_timezone = 'Australia/Perth' > datestyle = 'iso, dmy' > timezone = 'Australia/Perth' > lc_messages = 'English_Australia.1252' > lc_monetary = 'English_Australia.1252' > lc_numeric = 'English_Australia.1252' > lc_time = 'English_Australia.1252' > default_text_search_config = 'pg_catalog.english' > listen_addresses = '*' > port = 5432 > max_connections = 300 > shared_buffers = 500MB > work_mem = 32MB > maintenance_work_mem = 128MB > checkpoint_segments = 100 > random_page_cost = 2.0 > effective_cache_size = 1500MB > log_destination = 'stderr' > logging_collector = on > log_min_duration_statement = 500 > log_line_prefix = '%t ' > > Replicant postgresql.conf (essentially a copy from master with minimal > changes): > > dynamic_shared_memory_type = windows > wal_level = archive > max_standby_streaming_delay = 30s > wal_receiver_status_interval = 30s > log_line_prefix = '%t ' > log_timezone = 'Australia/Perth' > datestyle = 'iso, dmy' > timezone = 'Australia/Perth' > lc_messages = 'English_Australia.1252' > lc_monetary = 'English_Australia.1252' > lc_numeric = 'English_Australia.1252' > lc_time = 'English_Australia.1252' > default_text_search_config = 'pg_catalog.english' > listen_addresses = '*' > port = 5432 > max_connections = 300 > shared_buffers = 500MB > work_mem = 32MB > maintenance_work_mem = 128MB > checkpoint_segments = 100 > random_page_cost = 2.0 > effective_cache_size = 1500MB > log_destination = 'stderr' > logging_collector = on > log_min_duration_statement = 500 > log_line_prefix = '%t ' > > replicant recovery.conf: > > standby_mode = 'on' > primary_conninfo = 'host= user= > password= connect_timeou
Re: Database health check/auditing
Hi Tim, There are good continuously running apps to monitor postgres. IMHO, the most successful one is pgcenter. Here is the link. https://github.com/lesovsky/pgcenter Regards İbrahim On 16 Feb 2018 5:22 am, "Tim Cross" wrote: > Hi All, > > I was wondering if anyone has some pointers to > sites/repositories/resources for scripts to perform basic database > audits and health checks. > > situation: I have just commenced a DBA and developer role for an > organisation with a number of Postgres databases (9.4 and 9.6 > versions). There has been no dedicated DBA and a number of the databases > were setup by people with little to know Postgres or database > experience. I need to get an overview on what I'm dealing with and start > prioritising what to address first. > > It has been some years since I've done any real work with Postgres. Most > of my technical work over the last 10 years has been with Oracle. I > prefer to use scripts over GUI tools like pgAdmin and suspect that there > is probably some good resources out there with existing scripts I can > use as a starting point. > > Any pointers greatly appreciated. > > thanks, > > Tim > > > -- > Tim Cross > >
