[GENERAL] "FATAL: role "postgres" does not exist" after recover
I am using database Postgresql 9.0.1. For some reasons, my database was crashed. At first I found: DETAIL: The database cluster was initialized with PG_CONTROL_VERSION 0, but the server was compiled with PG_CONTROL_VERSION 903. I solved it by copying pg_control from a new cluster Then I got, FATAL: could not open directory "pg_notify": Not a directory Same thing I did here. Now the db is up. But I can't connect to the db. It says, FATAL: role "postgres" does not exist No roles in the db. When I use createuser it says the same. Any idea please.
Re: [GENERAL] "FATAL: role "postgres" does not exist" after recover
On 17/07/2011 5:19 PM, AI Rumman wrote: I am using database Postgresql 9.0.1. For some reasons, my database was crashed. At first I found: DETAIL: The database cluster was initialized with PG_CONTROL_VERSION 0, but the server was compiled with PG_CONTROL_VERSION 903. First, read and act on this: http://wiki.postgresql.org/wiki/Corruption -- Craig Ringer POST Newspapers 276 Onslow Rd, Shenton Park Ph: 08 9381 3088 Fax: 08 9388 2258 ABN: 50 008 917 717 http://www.postnewspapers.com.au/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Error in restoring a large object
Hello all, I am testing a backup and restore of a db in version 7.4.30. During the restore, after successfully restoring many BLOBS, I get the following error: pg_restore: restoring large object OID 318907 pg_restore: restoring large object OID 318909 pg_restore: restoring large object OID 318911 pg_restore: [archiver] could not write to large object (result: 4294967295, expected: 32768) pg_restore: *** aborted because of error What does it mean that the archiver "could not write" to the large object? How can I find out more information about the object in question (and possibly delete it)? Thanks, Chris
Re: [GENERAL] Error in restoring a large object
Chris McCormick writes: >I am testing a backup and restore of a db in version 7.4.30. During the > restore, after successfully restoring many BLOBS, I get the following error: How many is "many"? > pg_restore: restoring large object OID 318907 > pg_restore: restoring large object OID 318909 > pg_restore: restoring large object OID 318911 > pg_restore: [archiver] could not write to large object (result: 4294967295, > expected: 32768) > pg_restore: *** aborted because of error >What does it mean that the archiver "could not write" to the large > object? How can I find out more information about the object in question > (and possibly delete it)? Try looking into the postmaster log to see if there's any corresponding failure logged on the server side. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Building an home computer for best Poker Tracker performance
I'm about to buy a new desktop home computer and my main concern is best Poker Tracker performance. Poker Tracker uses PostgreSQL so I thought this will be the best place to ask. My DB is big, around 5M hands(big is relative of course), I use complex HUD(if you know what it is), run complex reports and play 12+ tables. Complex is relatively complex to other PT users, I don't know how it compared to other tasks. I have questions about CPU/RAM/SSD: CPU: *Will PostgreSQL benefit from using hyperthreading in i7 2600k CPU? Or will there be no noticeable performance change if I use i5 2500K CPU(i.e no HT, just 4 cores)? *Will there be noticeable performance improvements if I OC my CPU from 3.3ghz/3.4ghz(with turbo bust to 3.7ghz/3.8ghz) to 4.5ghz? RAM: *Will there be noticeable performance improvements if I will use 16GB RAM over 8GB RAM? I know big servers uses at least 16GB(often 32GB). *Will there be noticeable change between CAS 9 and CAS 8/7? 1333mhz/1600mgz ddr3 ram? SSD: Different SSD excel in different areas. I know that for general PC usage, 4K Q1 random read/write is the most important. What is the most important for PT3(and PostgreSQL) usage? Random? sequel? 4K/8K/16K / 32K/ +? 4K-64Thrd? 4K QD4/QD16/QD32 ? etc ... *I will use windows 7(x64) Any other recommendations? (regards to hardware, I will look for tuning after i'll buy the computer). Thanks. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Building-an-home-computer-for-best-Poker-Tracker-performance-tp4597798p4597798.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Building an home computer for best Poker Tracker performance
On 18/07/2011 9:37 AM, mdxxd wrote: CPU: *Will PostgreSQL benefit from using hyperthreading in i7 2600k CPU? Or will there be no noticeable performance change if I use i5 2500K CPU(i.e no HT, just 4 cores)? On a typical desktop system, hard disk I/O is the limiting factor rather than CPU, so I imagine you wouldn't see much difference between the two. PostgreSQL can only use one CPU core (or thread on HT CPUs) per query. If you run few big queries, dual core might even be enough. If you run lots and lots of tiny queries, you might benefit from lots of CPUs. Examine your usage on your current system - see pg_stat_activity - to get some idea what your usage is like. *Will there be noticeable performance improvements if I OC my CPU from 3.3ghz/3.4ghz(with turbo bust to 3.7ghz/3.8ghz) to 4.5ghz? Again, it depends on whether your queries are limited by hard disk or CPU. If they're limited by hard disk access, it won't make much difference at all. RAM: *Will there be noticeable performance improvements if I will use 16GB RAM over 8GB RAM? I know big servers uses at least 16GB(often 32GB). Depends on how big your database is. You want your database and indexes to fit entirely in RAM if possible. If it fits in 8GB but not in 16GB, the difference will be absolutely enormous. If it fits in 8GB then you won't see much difference going up to 16GB. Check your current total database size, including indexes, and work out how much RAM you need. Allow for growth. See the user manual for how to get your database size. Do *NOT* just get the size of the pg data directory. *Will there be noticeable change between CAS 9 and CAS 8/7? 1333mhz/1600mgz ddr3 ram? Probably only if your database is fully cached in RAM and the queries are moving lots of data rather than doing complex processing on smaller amounts of data. The usual rule of thumb on this list is that more RAM is better than faster RAM, but I know Greg Smith's testing has found that RAM speed and in particular how the CPU accesses the RAM does matter. See his book "PostgreSQL high performance" for a bit more information. SSD: Different SSD excel in different areas. I know that for general PC usage, 4K Q1 random read/write is the most important. What is the most important for PT3(and PostgreSQL) usage? Random? sequel? 4K/8K/16K / 32K/ +? 4K-64Thrd? 4K QD4/QD16/QD32 ? etc ... Most consumer quality SSDs will eat your data the first time you have a power cut. Be careful to get one with a supercapacitor or battery backup. See past mailing list discussion for which SSDs are safe. If your database fits entirely in RAM and you don't write to it especially fast you might not even need an SSD. A RAID 1 array of reasonable hard drives might be quite good enough. *I will use windows 7(x64) Any other recommendations? (regards to hardware, I will look for tuning after i'll buy the computer). In general, Pg is faster on linux. -- Craig Ringer POST Newspapers 276 Onslow Rd, Shenton Park Ph: 08 9381 3088 Fax: 08 9388 2258 ABN: 50 008 917 717 http://www.postnewspapers.com.au/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Building an home computer for best Poker Tracker performance
On Sun, Jul 17, 2011 at 7:37 PM, mdxxd wrote: > I'm about to buy a new desktop home computer and my main concern is best > Poker Tracker performance. > Poker Tracker uses PostgreSQL so I thought this will be the best place to > ask. > > My DB is big, around 5M hands(big is relative of course), I use complex > HUD(if you know what it is), run complex reports and play 12+ tables. > Complex is relatively complex to other PT users, I don't know how it > compared to other tasks. > > I have questions about CPU/RAM/SSD: > CPU: > *Will PostgreSQL benefit from using hyperthreading in i7 2600k CPU? Or will > there be no noticeable performance change if I use i5 2500K CPU(i.e no HT, > just 4 cores)? > *Will there be noticeable performance improvements if I OC my CPU from > 3.3ghz/3.4ghz(with turbo bust to 3.7ghz/3.8ghz) to 4.5ghz? Most likely 4 cores is plenty, unless pokertracker can run > 1 connection, since PostgreSQL basically runs one process for each connection. The difference from 3.7 to 4.5GHz is likely to be minimal, but if you run queries that take more than a few seconds then it might make a bit of a difference. > RAM: > *Will there be noticeable performance improvements if I will use 16GB RAM > over 8GB RAM? I know big servers uses at least 16GB(often 32GB). > *Will there be noticeable change between CAS 9 and CAS 8/7? 1333mhz/1600mgz > ddr3 ram? Unless each record is huge, I can't see 5M records needing more than 8 Gig, even with other stuff running. The change in ram will be pretty much linear assuming the CPU can take advantage of the faster RAM. I.e. if a CPU has a max bus frequency of 1333MHz going to 1600 MHz memory won't make any real difference. > > SSD: > Different SSD excel in different areas. I know that for general PC usage, 4K > Q1 random read/write is the most important. > What is the most important for PT3(and PostgreSQL) usage? Random? sequel? > 4K/8K/16K / 32K/ +? 4K-64Thrd? 4K QD4/QD16/QD32 ? etc ... SSDs can make a HUGE difference if you're doing a lot of random writes. I'm guessing your data will fit in memory, so there's no big advantage for read performance. Note that most consumer SSDs and many professional ones are not reliable in the event of a crash, so take nightly backups so you can fix things if your machine crashes hard and corrupts the db. If you're doing mostly reads, then the SSD will be no big advantage. > *I will use windows 7(x64) > > Any other recommendations? (regards to hardware, I will look for tuning > after i'll buy the computer). I'd expect a core i5 2500k to be plenty fast for pgsql performance. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Postgresql 9.0.1 Corrupted
*Database Version:* Postgresql 9.0.1 *OS:* Linux rumman.dhaka 2.6.18-92.el5xen #1 SMP Tue Jun 10 19:55:54 EDT 2008 i686 i686 i386 GNU/Linux *Installed from:* Source configure *Problem:* When I try to connect to postgres, I got - psql: FATAL: role "postgres" does not exist In fact, it seems to me that no role exists. *Problem Cause:* For some reason, filesystem of my server was corrupted. Our system admin used CENTOS repair tools and it damaged the database files. It created some of the directory as files. These are - - pg_notify - pg_xlog Some files were not readable during start. These were - pg_internal and pg_control in the global directory. At first, when I tried to start the server after CENTOS repair, I got the error message - DETAIL: The database cluster was initialized with PG_CONTROL_VERSION 0, but the server was compiled with PG_CONTROL_VERSION 903. I created a fresh cluster and copied the pg_control and pg_internal from this cluster to the corrupted cluster. Then, I tried to start and I got FATAL: could not open directory "pg_notify": Not a directory This was created as files instead of directory. I removed it and created the directory. At this point, the server started successfully. Then I tried to connect using psql command. But I got - psql: FATAL: role "postgres" does not exist I tried to use CRAETEUSER. But it can't connect to db. Please help.
Re: [GENERAL] Postgresql 9.0.1 Corrupted
On Jul 17, 2011, at 9:36 PM, AI Rumman wrote: > Database Version: Postgresql 9.0.1 > OS: Linux rumman.dhaka 2.6.18-92.el5xen #1 SMP Tue Jun 10 19:55:54 EDT 2008 > i686 i686 i386 GNU/Linux > Installed from: Source configure > Problem: > When I try to connect to postgres, I got - > psql: FATAL: role "postgres" does not exist > In fact, it seems to me that no role exists. > > Problem Cause: > > For some reason, filesystem of my server was corrupted. Our system admin used > CENTOS repair tools and it damaged the database files. > It created some of the directory as files. These are - > - pg_notify > - pg_xlog > Some files were not readable during start. These were - pg_internal and > pg_control in the global directory. > > At first, when I tried to start the server after CENTOS repair, I got the > error message - > DETAIL: The database cluster was initialized with PG_CONTROL_VERSION 0, but > the server was compiled with PG_CONTROL_VERSION 903. Your data is probably gone. Check when you last made backups. But before you do *anything* else, create a full filesystem level copy of the entire database directory, so that when you end up breaking things further by trying to recover it you'll have a "clean" copy of the corrupted database to start over from. Cheers, Steve -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
