[GENERAL] inconsistent backup?
hello all, don't ask why, but a customer created tables with foreign key constraints but with inconsistent data. Because of this he disabled all triggers (alter table foo disable trigger all). So far, so bad ... (he can't clean the data at the moment) In the dump there are the constraints, but NOT the disable triggers. In other words: no way to restore. What can we do now? Version: 9.2 Regards, akretschmer -- 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] unexpected pageaddr error in db log
On Thu, Dec 19, 2013 at 2:24 PM, Amit Langote wrote: > "unexpected pageaddr" log entry in this case means the standby reached > the end of existing WAL. > So, just before connecting to walsender for streaming replication, it logs > this. > Thanks for your reply. So this means I can safely omit this message?
Re: [GENERAL] inconsistent backup?
Am 19.Dez. 2013 um 09:41 schrieb Andreas Kretschmer : > hello all, > > don't ask why, but a customer created tables with foreign key constraints but > with inconsistent data. > > Because of this he disabled all triggers (alter table foo disable trigger > all). > So far, so bad ... > (he can't clean the data at the moment) > > In the dump there are the constraints, but NOT the disable triggers. In other > words: no way to restore. > > What can we do now? pg_restore has a --disable-triggers option, that could be of some help. Or you add the command to the top of the sql dump file. Ralf -- 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] Is it advisable to pg_upgrade directly from 9.0 to 9.3?
Hi Greg, I just wanted to know if you were able successfully upgrading from 9.0 to 9.3. I have been doing this upgrading this past week, but always ended up with unsuccessful upgrade. It will be great if you can share you knowledge on this. Thanks! -Laurent On Thu, Nov 7, 2013 at 2:07 PM, Greg Burek wrote: > On Wed, Nov 6, 2013 at 4:36 AM, Leonardo Carneiro > wrote: > >> I don't think that there will be too much trouble, as long as you follow >> every changelog tip (9.0->9.1, 9.1->9.2 and 9.2->9.3) >> >> > What if we don't follow the changelog tip? In this case, we have only the > 9.0 and 9.3 binaries installed and pg_upgrade directly to 9.3. Does that > cause fear? >
Re: [GENERAL] Multi Master Replication
> 2. With sync replication, you have coordination problems and > therefore it is never (at least IME) a win compared to master-slave > replication since all writes must occur in the same order in the set, > or you need global sequences, or such. *snip* > You will never get better read or write throughput, Better read throughput is trivial to achieve even with other solutions than multi-master replication. And for better write throughput, the developers of Postgres-XC (supported by NTT, among others) beg to differ: http://postgresxc.wikia.com/wiki/Postgres-XC_Wiki http://postgres-xc.sourceforge.net/ As does Bettina Kemme (of Postgres-R fame). Sincerely, Wolfgang -- 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] Multi Master Replication
On Thu, Dec 19, 2013 at 6:52 AM, Wolfgang Keller wrote: > > 2. With sync replication, you have coordination problems and > > therefore it is never (at least IME) a win compared to master-slave > > replication since all writes must occur in the same order in the set, > > or you need global sequences, or such. > > *snip* > > > You will never get better read or write throughput, > > Better read throughput is trivial to achieve even with other solutions > than multi-master replication. > > And for better write throughput, the developers of Postgres-XC > (supported by NTT, among others) beg to differ: > > http://postgresxc.wikia.com/wiki/Postgres-XC_Wiki > http://postgres-xc.sourceforge.net/ I am not quite sure what the point is. I am not sure you will get the same write extensibility if you list every table as replicated instead of partitioned. What Postgres-XC gives you ideally is a no-storage and multi-master coordination layer on top of master-slave data nodes. Some things may need to be replicated multi-master between data nodes but that's not a win write throughput-wise. I am btw a reasonable fan of Postgres-XC within its problem domain, but it is not a synchronous multi-master replication solution as far as write scaling goes. My point still holds, which is that synchronous multi-master replication will never beat master-slave in write throughput. My understanding of Postgres-XC is that you'd mark tables as replicated (instead of partitioned) when they are going to be joined against by different nodes and infrequently updated (and hence the write overhead is less of a problem than the cross-node join overhead). Am I way off-base with my understanding here? At any rate it isn't Postgres-XC (which is something very different than a typical "replication" setup, and I would describe it more as an advanced sharding solution). Best Wishes, Chris Travers > > > As does Bettina Kemme (of Postgres-R fame). > > Sincerely, > > Wolfgang > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Best Wishes, Chris Travers Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in. http://www.efficito.com/learn_more.shtml
Re: [GENERAL] unexpected pageaddr error in db log
On Thu, Dec 19, 2013 at 1:27 AM, wd wrote: > > On Thu, Dec 19, 2013 at 2:24 PM, Amit Langote wrote: > >> "unexpected pageaddr" log entry in this case means the standby reached >> the end of existing WAL. >> So, just before connecting to walsender for streaming replication, it >> logs this. >> > > > Thanks for your reply. So this means I can safely omit this message? > Yes, you can ignore that message. It message level was something more severe than "[LOG]" then it would be worth investigating further.
Re: [GENERAL] Installed postgres.app 9.3.1.0. pgadmin doesn't appear to see it
On 12/18/2013 09:36 PM, Bob Futrelle wrote: I uninstalled 9.2 before installing 9.3.1.0. The app is called Postgres93, it is version 9.3.1.0 I downloaded the latest pgAdmin, it is pgAdmin3 version 1.18.1 I have a database "MiniServer" which is supposed to use postgres as its Maintenance database. But there is no such database. Perhaps it can't find the DBs that were there? pg_upgrade requires pg_upgrade-boldbindir-Bnewbindir-dolddatadir-Dnewdatadir [option...] but I'm not sure what the 'old' values should be (since I uninstalled 9.2 - maybe I shouldn't have?) In my /Library/PostgreSQL/9.2/data/global I see 42 files fom 8K to 25K in size, most created last March - my data must be there. So that may be telling me to create an empty folder, /Library/PostgreSQL/9.3/ In my Postgres93 bundle there's a Contents/MacOS/postgres, a small unix executable. Is that the binary? But again there's no binary for 9.2. I was rolling along with 9.2 getting lots of good work done via Eclipse/JDBC. But now I'm stuck. Sorry for being so dense. But I've never been afraid to ask questions. The answers I get increase my understanding of whatever the topic is. Thanks in advance for helping a tyro. So lets try to sort out what happened here. I will go though what I think the situation is(with additional questions), you correct me if I am wrong. 1) You are using a Mac. What version of OS X? How did you install the old version/new version of Postgres? 2) You where running Postgres 9.2 and then you uninstalled that package. Under Postgres 9.2 everything worked. 3) You installed the Postgres 9.3 server package. 4) You installed pgAdmin 1.18.1 5) You are trying to run Postgres 9.3 over a 9.2 data directory. This should serve to start us out. - Bob -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pg_upgrade & tablespaces
Hello, I am trying to upgrade from 9.0.14 to 9.3. I am using the pg_upgrade utility. I need to use pg_upgrade because my production database is 800GB+ and with over 80 tablespaces and doing an export from 9.0 and importing to 9.3 would take at least 2 days. Currently I am testing on the development database which is only 100GB with a same number of tablespaces. I am working on FreeBSD with jails. So one jail contains 9.0 and the other 9.3. In the 93 jail I mount the data and binary directories for the 9.0 jail. Here is the command to check: pg_upgrade -b /home/jkregloh/pg_bin/ -B /usr/local/bin/ -d /home/jkregloh/pg_data/ -D /usr/local/pgsql/data/ -p 5452 -P 5451 -c As you can see the data and binary files for 9.0 are in /home/jkregloh/pg_bin/ and /home/jkregloh/pg_data/, while the 9.3 resides in the default location. When running the check it reports that both clusters are compatible. Once the actual process starts it will work fine until it starts up the 9.3 to copy data over. The problem that I am having is that pg_upgrade is creating the 93 files under the old directory and not the new one. So when 9.3 goes to import it doesn't find anything. Now, both versions can't share the same /data directory for obvious reasons. Is there any way to make pg_upgrade actually export the new 9.3 files into the 9.3 directory supplied in the pg_upgrade command? I am also open to any other upgrade ideas. Thanks, Joseph
Re: [GENERAL] pg_upgrade & tablespaces
On 12/19/2013 08:34 AM, Joseph Kregloh wrote: Hello, I am trying to upgrade from 9.0.14 to 9.3. I am using the pg_upgrade utility. I need to use pg_upgrade because my production database is 800GB+ and with over 80 tablespaces and doing an export from 9.0 and importing to 9.3 would take at least 2 days. Currently I am testing on the development database which is only 100GB with a same number of tablespaces. I am working on FreeBSD with jails. So one jail contains 9.0 and the other 9.3. In the 93 jail I mount the data and binary directories for the 9.0 jail. Here is the command to check: pg_upgrade -b /home/jkregloh/pg_bin/ -B /usr/local/bin/ -d /home/jkregloh/pg_data/ -D /usr/local/pgsql/data/ -p 5452 -P 5451 -c The only thing I have is, are the port numbers correct? I tend to use larger numbers for newer versions which, is why I am asking. As you can see the data and binary files for 9.0 are in /home/jkregloh/pg_bin/ and /home/jkregloh/pg_data/, while the 9.3 resides in the default location. When running the check it reports that both clusters are compatible. Once the actual process starts it will work fine until it starts up the 9.3 to copy data over. The problem that I am having is that pg_upgrade is creating the 93 files under the old directory and not the new one. So when 9.3 goes to import it doesn't find anything. Now, both versions can't share the same /data directory for obvious reasons. Is there any way to make pg_upgrade actually export the new 9.3 files into the 9.3 directory supplied in the pg_upgrade command? I am also open to any other upgrade ideas. Thanks, Joseph -- Adrian Klaver adrian.kla...@gmail.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] pg_upgrade & tablespaces
On 13-12-19 11:34 AM, Joseph Kregloh wrote: Hello, I am trying to upgrade from 9.0.14 to 9.3. I am using the pg_upgrade utility. I need to use pg_upgrade because my production database is 800GB+ and with over 80 tablespaces and doing an export from 9.0 and importing to 9.3 would take at least 2 days. Currently I am testing on the development database which is only 100GB with a same number of tablespaces. I am working on FreeBSD with jails. So one jail contains 9.0 and the other 9.3. In the 93 jail I mount the data and binary directories for the 9.0 jail. Here is the command to check: pg_upgrade -b /home/jkregloh/pg_bin/ -B /usr/local/bin/ -d /home/jkregloh/pg_data/ -D /usr/local/pgsql/data/ -p 5452 -P 5451 -c As you can see the data and binary files for 9.0 are in /home/jkregloh/pg_bin/ and /home/jkregloh/pg_data/, while the 9.3 resides in the default location. When running the check it reports that both clusters are compatible. Once the actual process starts it will work fine until it starts up the 9.3 to copy data over. The problem that I am having is that pg_upgrade is creating the 93 files under the old directory and not the new one. So when 9.3 goes to import it doesn't find anything. Now, both versions can't share the same /data directory for obvious reasons. Is there any way to make pg_upgrade actually export the new 9.3 files into the 9.3 directory supplied in the pg_upgrade command? I am also open to any other upgrade ideas. Thanks, Joseph Hi Joseph, Can you post your actual command syntax when you run the upgrade (not the check)? Maybe there'll be something wrong there we can spot. When I did it recently, I used something along the lines of: (PG93path)/pg_upgrade -d /opt/rg/data/pgsql90 -D /opt/rg/data/pgsql93 -b /(path to PG90 binaries)/bin -B /(path to 93 binaries) -v -p (oldport) -P (newport) Ziggy
Re: [GENERAL] pg_upgrade & tablespaces
Yes, the port numbers are correct. Both instances start by themselves on their own jails. On Thu, Dec 19, 2013 at 11:52 AM, Adrian Klaver wrote: > On 12/19/2013 08:34 AM, Joseph Kregloh wrote: > >> Hello, >> >> I am trying to upgrade from 9.0.14 to 9.3. I am using the pg_upgrade >> utility. I need to use pg_upgrade because my production database is >> 800GB+ and with over 80 tablespaces and doing an export from 9.0 and >> importing to 9.3 would take at least 2 days. >> >> Currently I am testing on the development database which is only 100GB >> with a same number of tablespaces. I am working on FreeBSD with jails. >> So one jail contains 9.0 and the other 9.3. In the 93 jail I mount the >> data and binary directories for the 9.0 jail. >> >> Here is the command to check: >> pg_upgrade -b /home/jkregloh/pg_bin/ -B /usr/local/bin/ >> -d /home/jkregloh/pg_data/ -D /usr/local/pgsql/data/ -p 5452 -P 5451 -c >> > > The only thing I have is, are the port numbers correct? I tend to use > larger numbers for newer versions which, is why I am asking. > > >> As you can see the data and binary files for 9.0 are in >> /home/jkregloh/pg_bin/ and /home/jkregloh/pg_data/, while the 9.3 >> resides in the default location. >> >> When running the check it reports that both clusters are compatible. >> Once the actual process starts it will work fine until it starts up the >> 9.3 to copy data over. The problem that I am having is that pg_upgrade >> is creating the 93 files under the old directory and not the new >> one. So when 9.3 goes to import it doesn't find anything. >> >> Now, both versions can't share the same /data directory for obvious >> reasons. Is there any way to make pg_upgrade actually export the new 9.3 >> files into the 9.3 directory supplied in the pg_upgrade command? I am >> also open to any other upgrade ideas. >> >> Thanks, >> Joseph >> > > > -- > Adrian Klaver > adrian.kla...@gmail.com >
[GENERAL] Re: After dump/restoring from 32bit 8.4-windows to 64bit 9.2.4-linux experiencing 10x slowdown on queries
Hi, did you find a resolution to this issue? I'm running into the same problem now! -- View this message in context: http://postgresql.1045698.n5.nabble.com/After-dump-restoring-from-32bit-8-4-windows-to-64bit-9-2-4-linux-experiencing-10x-slowdown-on-queries-tp5751526p5784087.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
[GENERAL] index and table corruption
Hi, We're looking for help with possible corruption of our indexes and tables. Seemingly in the middle of normal operations, we will run into errors like the below: ERROR: index "mv_visits_p03_n2" contains unexpected zero page at block 15939 ERROR: invalid page header in block 344713 of relation pg_tblspc/4376157/PG_9.1_201105231/16393/8367465 Following which the database continues on, but IO creeps up until finally the server becomes unresponsive. The database has never 'crashed' though A majority of the tables are the same each time, although new ones will come in, and old ones will go out. A total of about 84 out of 452 tables have gotten this error so far. We run postgres verion 9.1.2, installed via the PGDG rpms. The server runs centos5.6, and the disk backend is Netapp based SAN Its a 24CPU box, with 768G RAM. The database is about 1TB. Its a single database cluster. Things we've tried so far: - Everytime we run into the error, we restore the database from a previous snapshot (block level Netapp snapshot). Snapshots are taken with the postgres hot backup mode enabled, and are clean. They are block level, so ideally going back to a snapshot should remove any block level corruption that occurred on the device. - We set zero_damaged_pages = on, ran a full vacuum and re-index of 4 tables. Both the full vacuum and reindex completed successfully, with no errors. The same tables showed up when it failed again. - We've had the sysadmins check for errors with the hardware no errors so far about any h/w problems, either on the box, with the SAN switches, or on the filer. We are going to switch over to a different server on the same SAN backend, to see if that helps - We suspected it might have something to do with http://wiki.postgresql.org/wiki/20120924updaterelease, and upgraded to postgres 9.1.11, that hasn't helped. - We had shared_buffers set to 60G, and reduced that down to 8G, and then to 4G, suspecting problems with the background writer handling such high shared buffers, that hasn't helped either. Our postgres configuration is: Version: PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-50), 64-bit Updgraded to: PostgreSQL 9.1.11 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-50), 64-bit name | curent_setting --+ --- application_name | psql archive_command | /usr/bin/archiver.sh %f %p archive_mode | on checkpoint_completion_target | 0.8 checkpoint_segments | 25 checkpoint_timeout | 10min checkpoint_warning | 2min client_encoding | UTF8 commit_siblings | 25 custom_variable_classes | symmetric DateStyle| ISO, MDY default_statistics_target| 300 default_text_search_config | pg_catalog.english effective_cache_size | 128GB lc_messages | en_US.UTF-8 lc_monetary | en_US.UTF-8 lc_numeric | en_US.UTF-8 lc_time | en_US.UTF-8 listen_addresses | * log_checkpoints | on log_destination | syslog log_directory| /var/lib/pgsql/cmates/admin log_filename | postgresql-%a.log log_line_prefix | user=%u,db=%d,ip=%h log_min_duration_statement | 0 log_rotation_age | 1d log_rotation_size| 0 log_timezone | US/Pacific log_truncate_on_rotation | on logging_collector| off maintenance_work_mem | 32MB max_connections | 1500 max_locks_per_transaction| 1000 max_stack_depth | 2MB max_wal_senders | 5 port | 5432 search_path | activities, alert, announce, askme, audit, authentication, book, btdt, bulletinboard, cache, cas, cdc, cmates, cmdba, collection, dep, emailsubscription, emailvalidation, eventmail, feeds, friend, geo, inbox, invitation, ir , kkumar, merge, myvisitor, people, photos, prepsports, profile, provisioning, quiz, registrant_icons, registration, reunion, school, schoolfeed, shortlist, socialauth, statspack, story, symmetricds, target, yearbook, "$user", public shared_buffers | 8GB synchronous_commit | off syslog_facility | local0 syslog_ident | postgres TimeZone | US/Pacific vacuum_freeze_table_age | 0 wal_buffers | 16MB wal_level| archive wal_sync_method | fsync work_mem | 8MB (47 rows) Any help would be most appreciated! Thanks, Karthik Thanks, Karthik
Re: [GENERAL] index and table corruption
On 12/19/2013 12:42 PM, Anand Kumar, Karthik wrote: > ERROR: index "mv_visits_p03_n2" contains unexpected zero page at block > 15939 > ERROR: invalid page header in block 344713 of relation > pg_tblspc/4376157/PG_9.1_201105231/16393/8367465 I don't care what kind of checks your admins have performed. You have either bad memory, a bad controller card, SAN, or an otherwise unstable system. Do not continue to use this platform in its current state if you care about your data. > A majority of the tables are the same each time, although new ones will > come in, and old ones will go out. A total of about 84 out of 452 tables > have gotten this error so far. This is screaming memory or disk-based corruption. > We run postgres verion 9.1.2, installed via the PGDG rpms. > The server runs centos5.6, and the disk backend is Netapp based SAN > Its a 24CPU box, with 768G RAM. > The database is about 1TB. Its a single database cluster. That's a pretty nice system. :) > - We set zero_damaged_pages = on, ran a full vacuum and re-index of 4 > tables. Both the full vacuum and reindex completed successfully, with no > errors. The same tables showed up when it failed again. Because they're being corrupted again. > - We've had the sysadmins check for errors with the hardware no errors > so far about any h/w problems, either on the box, with the SAN switches, > or on the filer. We are going to switch over to a different server on the > same SAN backend, to see if that helps Do this. Do nothing else but this. Regardless of the checks the admins have run, you need to verify the data remains uncorrupted by removing variables. If this doesn't help, your SAN itself may be the problem. > - We suspected it might have something to do with > http://wiki.postgresql.org/wiki/20120924updaterelease, and upgraded to > postgres 9.1.11, that hasn't helped. You should do that anyway. There is literally no benefit to running an old minor release. Unlike 9.1 to 9.2, 9.1.2 to 9.1.11 costs nothing but a database restart. The amount of potential data corruption, crash, and planner bugs you avoid by doing so should never be underestimated. > - We had shared_buffers set to 60G, and reduced that down to 8G, and then > to 4G, suspecting problems with the background writer handling such high > shared buffers, that hasn't helped either. 60GB is way too high. Use 4GB or 8GB, like you said. 60GB means a very very long checkpoint, requires a far longer completion_timeout and checkpoint segments to really be useful, and greatly amplifies recovery time. > log_min_duration_statement | 0 You're logging every statement to pass through the server? If you have a lot of volume, that's a ton of IO all by itself. Enough that I would recommend putting the logs on another LUN entirely. > maintenance_work_mem | 32MB This is way too low. This setting is used for doing vacuum operations and other work necessary to maintain the database and its contents. You have more than enough memory to set this at 1GB. > synchronous_commit | off Turn this back on. So long as you're having corruption issues, you need this to be as stable as possible. Having transactions report commit before the WAL is successfully written means potentially losing transactions, especially with the frequent shutdowns these corruptions are causing. Aside from that, you're not likely to find much from asking here. You clearly have a hardware problem somewhere along the chain. Until that's resolved, you will have random corrupt data on your most frequently modified objects. Snapshot restores and WAL recovery can help repair the issues, but it's only a matter of time before a corruption is written right into the WAL itself, forcing you to do PITR instead. Good luck! -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-676-8870 stho...@optionshouse.com __ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email -- 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] index and table corruption
Thanks Shaun! Yes, we're getting synchronous_commit on right now. The log_min_duration was briefly set to 0 at the time I sent out the post, just to see what statements were logged right before everything went to hell. Didn't yield much since we very quickly realized we couldn't cope with the volume of logs. We also noticed that when trying to recover from a snapshot and replay archived wal logs, it would corrupt right away, in under an hour. When recovering from snapshots *without* replaying wal logs, we go on for a day or two without the problem, so it does seem like wal logs are probably not being flushed to disk as expected. Will update once we get onto the new h/w to see if that fixes it. Thanks, Karthik -- 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] pg_upgrade & tablespaces
Here is the output of my last test run:
[pgsql@postgres-93-upgrade ~]$ time pg_upgrade -b /home/jkregloh/pg_bin/ -B
/usr/local/bin/ -d /home/jkregloh/pg_data/data -D /usr/local/pgsql/data/ -p
5452 -P 5451
Performing Consistency Checks
-
Checking cluster versions ok
Checking database user is a superuser ok
Checking for prepared transactions ok
Checking for reg* system OID user data typesok
Checking for contrib/isn with bigint-passing mismatch ok
Creating dump of global objects ok
Creating dump of database schemas
ok
Checking for presence of required libraries ok
Checking database user is a superuser ok
Checking for prepared transactions ok
If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.
Performing Upgrade
--
Analyzing all rows in the new cluster ok
Freezing all rows on the new clusterok
Deleting files from new pg_clog ok
Copying old pg_clog to new server ok
Setting next transaction ID for new cluster ok
Setting oldest multixact ID on new cluster ok
Resetting WAL archives ok
Setting frozenxid counters in new cluster ok
Restoring global objects in the new cluster ok
Adding support functions to new cluster ok
Restoring database schemas in the new cluster
ok
Removing support functions from new cluster ok
Copying user relation files
...l/data/drupal_dbspace/PG_9.0_201008051/2752430/10913518
error while copying relation "pg_catalog.pg_largeobject"
("/usr/local/pgsql/data/drupal_dbspace/PG_9.0_201008051/2752430/10913518"
to "/usr/local/pgsql/data/drupal_dbspace/PG_9.3_201306121/16499/12301"): No
such file or directory
Failure, exiting
real2m10.913s
user0m5.691s
sys 0m10.525s
--
Listing of that directory in the 9.0 folder:
[pgsql@postgres-93-upgrade ~]$ ls -la
/home/jkregloh/pg_data/data/drupal_dbspace/
total 19
drwx-- 4 pgsql pgsql 4 Jun 8 2013 .
drwx-- 38 pgsql pgsql 46 Dec 19 20:18 ..
drwx-- 4 pgsql pgsql 4 Oct 20 2011 PG_9.0_201008051
--
Listing of that directory in the 9.3 folder:
[pgsql@postgres-93-upgrade ~]$ ls -ls /usr/local/pgsql/data/drupal_dbspace/
total 4
4 drwx-- 3 pgsql pgsql 3 Dec 19 20:18 PG_9.3_201306121
So what I get from this is that it does create the correct 9.3 files in the
new location, however it cannot copy the relation over because the old data
is in the /home/jkregloh/pg_data/data/drupal_dbspace/ not in
/usr/local/pgsql/data/drupal_dbspace/ as the pg_upgrade query tries to do.
-Joseph
On Thu, Dec 19, 2013 at 12:02 PM, Ziggy Skalski wrote:
> On 13-12-19 11:34 AM, Joseph Kregloh wrote:
>
> Hello,
>
> I am trying to upgrade from 9.0.14 to 9.3. I am using the pg_upgrade
> utility. I need to use pg_upgrade because my production database is 800GB+
> and with over 80 tablespaces and doing an export from 9.0 and importing to
> 9.3 would take at least 2 days.
>
> Currently I am testing on the development database which is only 100GB
> with a same number of tablespaces. I am working on FreeBSD with jails. So
> one jail contains 9.0 and the other 9.3. In the 93 jail I mount the data
> and binary directories for the 9.0 jail.
>
> Here is the command to check:
> pg_upgrade -b /home/jkregloh/pg_bin/ -B /usr/local/bin/
> -d /home/jkregloh/pg_data/ -D /usr/local/pgsql/data/ -p 5452 -P 5451 -c
>
> As you can see the data and binary files for 9.0 are in
> /home/jkregloh/pg_bin/ and /home/jkregloh/pg_data/, while the 9.3 resides
> in the default location.
>
> When running the check it reports that both clusters are compatible.
> Once the actual process starts it will work fine until it starts up the 9.3
> to copy data over. The problem that I am having is that pg_upgrade is
> creating the 93 files under the old directory and not the new one. So
> when 9.3 goes to import it doesn't find anything.
>
> Now, both versions can't share the same /data directory for obvious
> reasons. Is there any way to make pg_upgrade actually export the new 9.3
> files into the 9.3 directory supplied in the pg_upgrade command? I am also
> open to any other upgrade ideas.
>
> Thanks,
> Joseph
>
>
> Hi Joseph,
>
> Can you post your actual command syntax when you run the upgrade (not the
> check)? Maybe there'll be something wrong there we can spot.
> When I did it recently, I used something along the lines of:
>
> (PG93path)/pg_upgrade -d /opt/rg/data/pgsql90 -D /opt/rg/data/pgs
Re: [GENERAL] pg_upgrade & tablespaces
On 12/19/2013 12:27 PM, Joseph Kregloh wrote: Here is the output of my last test run: So what I get from this is that it does create the correct 9.3 files in the new location, however it cannot copy the relation over because the old data is in the /home/jkregloh/pg_data/data/drupal_dbspace/ not in /usr/local/pgsql/data/drupal_dbspace/ as the pg_upgrade query tries to do. So what does mount show? -Joseph -- Adrian Klaver adrian.kla...@gmail.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] index and table corruption
"Anand Kumar, Karthik" writes: > Thanks Shaun! > > Yes, we're getting synchronous_commit on right now. > > The log_min_duration was briefly set to 0 at the time I sent out the post, > just to see what statements were logged right before everything went to > hell. Didn't yield much since we very quickly realized we couldn't cope > with the volume of logs. > > We also noticed that when trying to recover from a snapshot and replay > archived wal logs, it would corrupt right away, in under an hour. When > recovering from snapshots *without* replaying wal logs, we go on for a day > or two without the problem, so it does seem like wal logs are probably not > being flushed to disk as expected. Make sure your snapshots are atomic as you probably assume they are and in fact must be if you expect a consistent cluster after startup and crash recovery. That is, if you are doing snaps at random times and not wrapping with pgstart/stop backup() *and* replaying WAL till concisconsistent recovery point. If you're snapping something like a remote-site mirror running SAN block-level replication, unless the snap is done at the end of flushing all changed blocks since last tick, then the image you're snapping may not be consistent. I say that because, I came into a company that had been doing snaps this way since eons ago and thought that since the clusters would start up and could perform trivial checks, things were OK. As soon aas you subjected an instance dirived this way however with something wide-ranging such as an all-table vac/analyze, dumpall... etc, soon after launching the foo, corruption was observed. FWIW > > Will update once we get onto the new h/w to see if that fixes it. > > Thanks, > Karthik -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net p: 312.241.7800 -- 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] pg_upgrade & tablespaces
I'm not sure what you mean by that question. -Joseph On Thu, Dec 19, 2013 at 3:41 PM, Adrian Klaver wrote: > On 12/19/2013 12:27 PM, Joseph Kregloh wrote: > >> Here is the output of my last test run: >> >> > >> So what I get from this is that it does create the correct 9.3 files in >> the new location, however it cannot copy the relation over because the >> old data is in the /home/jkregloh/pg_data/data/drupal_dbspace/ not in >> /usr/local/pgsql/data/drupal_dbspace/ as the pg_upgrade query tries to >> do. >> > > So what does mount show? > > >> -Joseph >> >>> >>> >> > > > -- > Adrian Klaver > adrian.kla...@gmail.com >
Re: [GENERAL] pg_upgrade & tablespaces
On Thu, Dec 19, 2013 at 12:27 PM, Joseph Kregloh wrote: > So what I get from this is that it does create the correct 9.3 files in the > new location, however it cannot copy the relation over because the old data > is in the /home/jkregloh/pg_data/data/drupal_dbspace/ not in > /usr/local/pgsql/data/drupal_dbspace/ as the pg_upgrade query tries to do. Can you show what ls -l /home/jkregloh/pg_data/data/pg_tblspc/ prints, please? -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979 gray...@gmail.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] pg_upgrade & tablespaces
On 12/19/2013 12:46 PM, Joseph Kregloh wrote: I'm not sure what you mean by that question. When you run the mount command in the jail what does it show? -Joseph -- Adrian Klaver adrian.kla...@gmail.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] pg_upgrade & tablespaces
[pgsql@postgres-93-upgrade ~]$ ls -l /home/jkregloh/pg_data/data/pg_tblspc/ lrwxr-xr-x 1 pgsql pgsql41 Dec 19 19:53 11047389 -> /home/jkregloh/pg_data/data/stats_dbspace lrwxr-xr-x 1 pgsql pgsql44 Dec 19 19:53 11047390 -> /home/jkregloh/pg_data/data/stats_indexspace lrwxr-xr-x 1 pgsql pgsql49 Dec 19 19:53 11047391 -> /home/jkregloh/pg_data/data/stats_staging_dbspace lrwxr-xr-x 1 pgsql pgsql52 Dec 19 19:53 11047392 -> /home/jkregloh/pg_data/data/stats_staging_indexspace lrwxr-xr-x 1 pgsql pgsql44 Dec 19 19:53 22319 -> /home/jkregloh/pg_data/data/datapipe_dbspace lrwxr-xr-x 1 pgsql pgsql47 Dec 19 19:53 22320 -> /home/jkregloh/pg_data/data/datapipe_indexspace lrwxr-xr-x 1 pgsql pgsql46 Dec 19 19:53 22321 -> /home/jkregloh/pg_data/data/datapipe_zlogspace lrwxr-xr-x 1 pgsql pgsql44 Dec 19 19:53 22322 -> /home/jkregloh/pg_data/data/p3_basic_dbspace lrwxr-xr-x 1 pgsql pgsql47 Dec 19 19:53 22323 -> /home/jkregloh/pg_data/data/p3_basic_indexspace lrwxr-xr-x 1 pgsql pgsql38 Dec 19 19:53 22324 -> /home/jkregloh/pg_data/data/p3_dbspace lrwxr-xr-x 1 pgsql pgsql41 Dec 19 19:53 22325 -> /home/jkregloh/pg_data/data/p3_indexspace lrwxr-xr-x 1 pgsql pgsql59 Dec 19 19:53 22326 -> /home/jkregloh/pg_data/data/p3_dd_tablespaces/list_data_001 lrwxr-xr-x 1 pgsql pgsql60 Dec 19 19:53 22327 -> /home/jkregloh/pg_data/data/p3_dd_tablespaces/list_index_001 lrwxr-xr-x 1 pgsql pgsql43 Dec 19 19:53 22328 -> /home/jkregloh/pg_data/data/p3_zlog_dbspace lrwxr-xr-x 1 pgsql pgsql46 Dec 19 19:53 22329 -> /home/jkregloh/pg_data/data/p3_zlog_indexspace lrwxr-xr-x 1 pgsql pgsql41 Dec 19 19:53 22330 -> /home/jkregloh/pg_data/data/sling_dbspace lrwxr-xr-x 1 pgsql pgsql44 Dec 19 19:53 22331 -> /home/jkregloh/pg_data/data/sling_indexspace lrwxr-xr-x 1 pgsql pgsql51 Dec 19 19:53 2260532 -> /home/jkregloh/pg_data/data/p3_olap_staging_dbspace lrwxr-xr-x 1 pgsql pgsql54 Dec 19 19:53 2260533 -> /home/jkregloh/pg_data/data/p3_olap_staging_indexspace lrwxr-xr-x 1 pgsql pgsql52 Dec 19 19:53 2283998 -> /home/jkregloh/pg_data/data/p3_olap_datamart_dbspace lrwxr-xr-x 1 pgsql pgsql55 Dec 19 19:53 2283999 -> /home/jkregloh/pg_data/data/p3_olap_datamart_indexspace lrwxr-xr-x 1 pgsql pgsql59 Dec 19 19:53 2327012 -> /home/jkregloh/pg_data/data/p3_dd_tablespaces/list_data_002 lrwxr-xr-x 1 pgsql pgsql59 Dec 19 19:53 2327013 -> /home/jkregloh/pg_data/data/p3_dd_tablespaces/list_data_003 lrwxr-xr-x 1 pgsql pgsql59 Dec 19 19:53 2327014 -> /home/jkregloh/pg_data/data/p3_dd_tablespaces/list_data_004 lrwxr-xr-x 1 pgsql pgsql59 Dec 19 19:53 2327015 -> /home/jkregloh/pg_data/data/p3_dd_tablespaces/list_data_005 lrwxr-xr-x 1 pgsql pgsql59 Dec 19 19:53 2327016 -> /home/jkregloh/pg_data/data/p3_dd_tablespaces/list_data_006 lrwxr-xr-x 1 pgsql pgsql59 Dec 19 19:53 2327017 -> /home/jkregloh/pg_data/data/p3_dd_tablespaces/list_data_007 lrwxr-xr-x 1 pgsql pgsql59 Dec 19 19:53 2327018 -> /home/jkregloh/pg_data/data/p3_dd_tablespaces/list_data_008 lrwxr-xr-x 1 pgsql pgsql59 Dec 19 19:53 2327019 -> /home/jkregloh/pg_data/data/p3_dd_tablespaces/list_data_009 lrwxr-xr-x 1 pgsql pgsql59 Dec 19 19:53 2327020 -> /home/jkregloh/pg_data/data/p3_dd_tablespaces/list_data_010 lrwxr-xr-x 1 pgsql pgsql60 Dec 19 19:53 2327021 -> /home/jkregloh/pg_data/data/p3_dd_tablespaces/list_index_002 lrwxr-xr-x 1 pgsql pgsql60 Dec 19 19:53 2327022 -> /home/jkregloh/pg_data/data/p3_dd_tablespaces/list_index_003 lrwxr-xr-x 1 pgsql pgsql60 Dec 19 19:53 2327023 -> /home/jkregloh/pg_data/data/p3_dd_tablespaces/list_index_004 lrwxr-xr-x 1 pgsql pgsql60 Dec 19 19:53 2327024 -> /home/jkregloh/pg_data/data/p3_dd_tablespaces/list_index_005 lrwxr-xr-x 1 pgsql pgsql60 Dec 19 19:53 2327025 -> /home/jkregloh/pg_data/data/p3_dd_tablespaces/list_index_006 lrwxr-xr-x 1 pgsql pgsql60 Dec 19 19:53 2327026 -> /home/jkregloh/pg_data/data/p3_dd_tablespaces/list_index_007 lrwxr-xr-x 1 pgsql pgsql60 Dec 19 19:53 2327027 -> /home/jkregloh/pg_data/data/p3_dd_tablespaces/list_index_008 lrwxr-xr-x 1 pgsql pgsql60 Dec 19 19:53 2327028 -> /home/jkregloh/pg_data/data/p3_dd_tablespaces/list_index_009 lrwxr-xr-x 1 pgsql pgsql60 Dec 19 19:53 2327029 -> /home/jkregloh/pg_data/data/p3_dd_tablespaces/list_index_010 lrwxr-xr-x 1 pgsql pgsql42 Dec 19 19:53 2752416 -> /home/jkregloh/pg_data/data/drupal_dbspace lrwxr-xr-x 1 pgsql pgsql45 Dec 19 19:53 2796385 -> /home/jkregloh/pg_data/data/drupal_indexspace lrwxr-xr-x 1 pgsql pgsql55 Dec 19 19:53 5819045 -> /home/jkregloh/pg_data/data/p3_ord_list_dbspace/january lrwxr-xr-x 1 pgsql pgsql56 Dec 19 19:53 5819046 -> /home/jkregloh/pg_data/data/p3_ord_list_dbspace/february lrwxr-xr-x
Re: [GENERAL] pg_upgrade & tablespaces
Within the jail it would be: [pgsql@postgres-93-upgrade ~]$ mount sata-data/usr/jails/postgres-93-upgrade on / (zfs, local, nfsv4acls) But I am mounting those directories from the host, which will be: [root@v1 /postgres_data/p3-dev-db-93]# mount -l | grep postgres-93-upgrade sata-data/usr/jails/postgres-93-upgrade on /usr/jails/postgres-93-upgrade (zfs, local, nfsv4acls) /usr/jails/basejail on /usr/jails/postgres-93-upgrade/basejail (nullfs, local, read-only) devfs on /usr/jails/postgres-93-upgrade/dev (devfs, local, multilabel) fdescfs on /usr/jails/postgres-93-upgrade/dev/fd (fdescfs) procfs on /usr/jails/postgres-93-upgrade/proc (procfs, local) /usr/jails/postgres-90-upgrade/usr/local/bin on /usr/jails/postgres-93-upgrade/home/jkregloh/pg_bin (nullfs, local) /dev_db/stop_db/postgres_data on /usr/jails/postgres-93-upgrade/home/jkregloh/pg_data/data (nullfs, local) /dev_db/stop_db/postgres_archive_data on /usr/jails/postgres-93-upgrade/home/jkregloh/pg_data/data_archive (nullfs, local) On Thu, Dec 19, 2013 at 3:49 PM, Adrian Klaver wrote: > On 12/19/2013 12:46 PM, Joseph Kregloh wrote: > >> I'm not sure what you mean by that question. >> > > When you run the mount command in the jail what does it show? > > >> -Joseph >> >> >> >> > -- > Adrian Klaver > adrian.kla...@gmail.com >
Re: [GENERAL] pg_upgrade & tablespaces
On Thu, Dec 19, 2013 at 11:34:24AM -0500, Joseph Kregloh wrote: > Hello, > > I am trying to upgrade from 9.0.14 to 9.3. I am using the pg_upgrade utility. > I > need to use pg_upgrade because my production database is 800GB+ and with over > 80 tablespaces and doing an export from 9.0 and importing to 9.3 would take at > least 2 days. > > Currently I am testing on the development database which is only 100GB with a > same number of tablespaces. I am working on FreeBSD with jails. So one jail > contains 9.0 and the other 9.3. In the 93 jail I mount the data and binary > directories for the 9.0 jail. Why don't you do run pg_upgrade in the same jail then just move the files over to the new jail? That should work better. I am unclear how a cross-jail upgrade would work at all. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- 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] pg_upgrade & tablespaces
On 12/19/2013 12:53 PM, Bruce Momjian wrote: Currently I am testing on the development database which is only 100GB with a >same number of tablespaces. I am working on FreeBSD with jails. So one jail >contains 9.0 and the other 9.3. In the 93 jail I mount the data and binary >directories for the 9.0 jail. Why don't you do run pg_upgrade in the same jail then just move the files over to the new jail? That should work better. I am unclear how a cross-jail upgrade would work at all. or just leave the 9.3 in the 'postgres' jail, which to me makes as much sense as anything. 80 tablespaces is a mess no matter how you slice it. -- john r pierce 37N 122W somewhere on the middle of the left coast -- 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] pg_upgrade & tablespaces
It's easier to keep things segregated. It is not anymore different than doing the upgrade in the same jail. Which at the end of the day you are doing the upgrade in the same jail, because at the end of the day pg_upgrade just needs the old data an binary to start and create some dump files. But the real problem here is with the table spaces. Because in order to copy the relation over I would need to mount the old data to the /usr/local/pgsql/data on the new jail. The relation would be there and would finish successfully(I did this exercise). However the 9.3 install would be in a different directory, say /usr/local/pgsql_93 and will not have the data files because they now live in the old install location. -Joseph On Thu, Dec 19, 2013 at 3:53 PM, Bruce Momjian wrote: > On Thu, Dec 19, 2013 at 11:34:24AM -0500, Joseph Kregloh wrote: > > Hello, > > > > I am trying to upgrade from 9.0.14 to 9.3. I am using the pg_upgrade > utility. I > > need to use pg_upgrade because my production database is 800GB+ and with > over > > 80 tablespaces and doing an export from 9.0 and importing to 9.3 would > take at > > least 2 days. > > > > Currently I am testing on the development database which is only 100GB > with a > > same number of tablespaces. I am working on FreeBSD with jails. So one > jail > > contains 9.0 and the other 9.3. In the 93 jail I mount the data and > binary > > directories for the 9.0 jail. > > Why don't you do run pg_upgrade in the same jail then just move the > files over to the new jail? That should work better. I am unclear how > a cross-jail upgrade would work at all. > > -- > Bruce Momjian http://momjian.us > EnterpriseDB http://enterprisedb.com > > + Everyone has their own god. + >
Re: [GENERAL] pg_upgrade & tablespaces
On Thu, Dec 19, 2013 at 12:49 PM, Joseph Kregloh wrote: > On Thu, Dec 19, 2013 at 3:46 PM, Sergey Konoplev wrote: >> On Thu, Dec 19, 2013 at 12:27 PM, Joseph Kregloh >> wrote: >> > So what I get from this is that it does create the correct 9.3 files in >> > the >> > new location, however it cannot copy the relation over because the old >> > data >> > is in the /home/jkregloh/pg_data/data/drupal_dbspace/ not in >> > /usr/local/pgsql/data/drupal_dbspace/ as the pg_upgrade query tries to >> > do. >> >> Can you show what ls -l /home/jkregloh/pg_data/data/pg_tblspc/ prints, >> please? >> > [pgsql@postgres-93-upgrade ~]$ ls -l /home/jkregloh/pg_data/data/pg_tblspc/ > lrwxr-xr-x 1 pgsql pgsql41 Dec 19 19:53 11047389 -> > /home/jkregloh/pg_data/data/stats_dbspace > lrwxr-xr-x 1 pgsql pgsql44 Dec 19 19:53 11047390 -> > /home/jkregloh/pg_data/data/stats_indexspace > lrwxr-xr-x 1 pgsql pgsql49 Dec 19 19:53 11047391 -> > /home/jkregloh/pg_data/data/stats_staging_dbspace Bruce, may be it's a silly question, but the above makes me think so. I always keep tablespaces in locations different from the main data dir, and never faced something like this. Doesn't pg_upgrade do a stright replace of -d dir with -D dir everywhere in paths? ps. Joseph, please, don't use top-posting, see http://en.wikipedia.org/wiki/Posting_style#Interleaved_style. -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979 gray...@gmail.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] pg_upgrade & tablespaces
On 12/19/2013 1:06 PM, Joseph Kregloh wrote: It's easier to keep things segregated. It is not anymore different than doing the upgrade in the same jail. Which at the end of the day you are doing the upgrade in the same jail, because at the end of the day pg_upgrade just needs the old data an binary to start and create some dump files. pg_upgrade needs to access the old data AND all the tablespaces at the same paths as the old server sees them AND the new data and tablespaces at the same path as the NEW server sees them. if the two servers are in different jails, I don't see how you could make that work... if you run pg_upgrade in the host system, then all the paths are different for both sets of data and tablespaces. -- john r pierce 37N 122W somewhere on the middle of the left coast -- 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] pg_upgrade & tablespaces
On 12/19/2013 01:06 PM, Joseph Kregloh wrote: It's easier to keep things segregated. It is not anymore different than doing the upgrade in the same jail. Which at the end of the day you are doing the upgrade in the same jail, because at the end of the day pg_upgrade just needs the old data an binary to start and create some dump files. But the real problem here is with the table spaces. Because in order to copy the relation over I would need to mount the old data to the /usr/local/pgsql/data on the new jail. The relation would be there and would finish successfully(I did this exercise). However the 9.3 install would be in a different directory, say /usr/local/pgsql_93 and will not have the data files because they now live in the old install location. Not sure all of this but I do have this question: In your original post you have: pg_upgrade -b /home/jkregloh/pg_bin/ -B /usr/local/bin/ -d /home/jkregloh/pg_data/ -D /usr/local/pgsql/data/ -p 5452 -P 5451 -c Note: -d /home/jkregloh/pg_data/ In your mount info you have: /dev_db/stop_db/postgres_data on /usr/jails/postgres-93-upgrade/home/jkregloh/pg_data/data (nullfs, local) If I am following correctly should it not be: -d /home/jkregloh/pg_data/data -Joseph -- Adrian Klaver adrian.kla...@gmail.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] pg_upgrade & tablespaces
On Thu, Dec 19, 2013 at 01:08:18PM -0800, Sergey Konoplev wrote: > On Thu, Dec 19, 2013 at 12:49 PM, Joseph Kregloh > wrote: > > On Thu, Dec 19, 2013 at 3:46 PM, Sergey Konoplev wrote: > >> On Thu, Dec 19, 2013 at 12:27 PM, Joseph Kregloh > >> wrote: > >> > So what I get from this is that it does create the correct 9.3 files in > >> > the > >> > new location, however it cannot copy the relation over because the old > >> > data > >> > is in the /home/jkregloh/pg_data/data/drupal_dbspace/ not in > >> > /usr/local/pgsql/data/drupal_dbspace/ as the pg_upgrade query tries to > >> > do. > >> > >> Can you show what ls -l /home/jkregloh/pg_data/data/pg_tblspc/ prints, > >> please? > >> > > [pgsql@postgres-93-upgrade ~]$ ls -l /home/jkregloh/pg_data/data/pg_tblspc/ > > lrwxr-xr-x 1 pgsql pgsql41 Dec 19 19:53 11047389 -> > > /home/jkregloh/pg_data/data/stats_dbspace > > lrwxr-xr-x 1 pgsql pgsql44 Dec 19 19:53 11047390 -> > > /home/jkregloh/pg_data/data/stats_indexspace > > lrwxr-xr-x 1 pgsql pgsql49 Dec 19 19:53 11047391 -> > > /home/jkregloh/pg_data/data/stats_staging_dbspace > > Bruce, may be it's a silly question, but the above makes me think so. > I always keep tablespaces in locations different from the main data > dir, and never faced something like this. > > Doesn't pg_upgrade do a stright replace of -d dir with -D dir > everywhere in paths? pg_upgrade is looking at the data dir, the database oid, and relfilenode to get the old path, and does the same for the new path. Tablespaces point to the same location in old and new clusters --- only a subdirectory PG_VERISON is different. Is /home/jkregloh/pg_data/data also your default cluster directory? If so, having tablespaces inside of there will not work well as they will continue to be stored in the old cluster's data directory. Those will not be renamed/relocated by pg_upgrade. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- 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] index and table corruption
Hi Jerry, Thanks for the suggestion Yes, until about a month ago, we weren't wrapping our snapshots with pg_start_backup and pg_stop_backup. Same reason as you mentioned, the database would start up and "trivial checks" would be okay, and so we figured "why write a script?". However we did change that a month or so ago ago, and have had the problem after that. Every snapshot we have tried to actually recover from has been wrapped in a pg_start_backup and pg_stop_backup, so we are leaning more towards server/disk corruption at this time. We also synced our snapshot to an alternate SAN, and ran a script to update every row of every table, and do a full vacuum and reindex of every table, and there were no error messages about bad blocks. Thanks, Karthik -- 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] pg_upgrade & tablespaces
On Thu, Dec 19, 2013 at 01:14:15PM -0800, John R Pierce wrote: > On 12/19/2013 1:06 PM, Joseph Kregloh wrote: > >It's easier to keep things segregated. It is not anymore different > >than doing the upgrade in the same jail. Which at the end of the > >day you are doing the upgrade in the same jail, because at the end > >of the day pg_upgrade just needs the old data an binary to start > >and create some dump files. > > pg_upgrade needs to access the old data AND all the tablespaces at > the same paths as the old server sees them AND the new data and > tablespaces at the same path as the NEW server sees them. if the > two servers are in different jails, I don't see how you could make > that work... if you run pg_upgrade in the host system, then all the > paths are different for both sets of data and tablespaces. The big question is should pg_upgrade be checking for this situation in --check mode, and if so, what should it check for? -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Best way to sync possibly corrupted data?
HI, We have an issue with possibly corrupt data in our postgresql server. Errors like: ERROR: index "photos_p00_n2" contains unexpected zero page at block 0 ERROR: invalid page header in block 12707 of relation pg_tblspc/5020557/PG_9.1_201105231/16393/9014673 Thanks to all the suggestions from this list. We are in the process of moving our database out to a different server, and we'll then set zero_dameged_pages to on, run a full vacuum and reindex. The question I have is – what is the best method to transfer the data over to ensure we don't copy over bad/corrupt data? I would think a filesystem based copy (rsync, etc) should be avoided, and a pg_dump with a new initdb is best? Thanks, Karthik
Re: [GENERAL] pg_upgrade & tablespaces
On Thu, Dec 19, 2013 at 1:18 PM, Bruce Momjian wrote: > On Thu, Dec 19, 2013 at 01:08:18PM -0800, Sergey Konoplev wrote: >> On Thu, Dec 19, 2013 at 12:49 PM, Joseph Kregloh >> wrote: >> > On Thu, Dec 19, 2013 at 3:46 PM, Sergey Konoplev wrote: >> >> Can you show what ls -l /home/jkregloh/pg_data/data/pg_tblspc/ prints, >> >> please? >> >> >> > [pgsql@postgres-93-upgrade ~]$ ls -l /home/jkregloh/pg_data/data/pg_tblspc/ >> > lrwxr-xr-x 1 pgsql pgsql41 Dec 19 19:53 11047389 -> >> > /home/jkregloh/pg_data/data/stats_dbspace >> >> Doesn't pg_upgrade do a stright replace of -d dir with -D dir >> everywhere in paths? > > pg_upgrade is looking at the data dir, the database oid, and relfilenode > to get the old path, and does the same for the new path. Tablespaces > point to the same location in old and new clusters --- only a > subdirectory PG_VERISON is different. > > Is /home/jkregloh/pg_data/data also your default cluster directory? If > so, having tablespaces inside of there will not work well as they will > continue to be stored in the old cluster's data directory. Those will > not be renamed/relocated by pg_upgrade. The thing is that /home/jkregloh/pg_data/data is his 9.0's cluster directory and /usr/local/pgsql/data/ is 9.3's one. And pg_upgrade tries to copy /usr/local/pgsql/data/drupal_dbspace/PG_9.0_201008051/2752430/10913518" to /usr/local/pgsql/data/drupal_dbspace/PG_9.3_201306121/16499/12301. In other words pg_upgrade thinks that the old tablespace is located in the same cluster directory as the new one. That made me think that it just replaces the cluster directory subpath everywhere. -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979 gray...@gmail.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] pg_upgrade & tablespaces
On Thu, Dec 19, 2013 at 4:14 PM, John R Pierce wrote: > On 12/19/2013 1:06 PM, Joseph Kregloh wrote: > >> It's easier to keep things segregated. It is not anymore different than >> doing the upgrade in the same jail. Which at the end of the day you are >> doing the upgrade in the same jail, because at the end of the day >> pg_upgrade just needs the old data an binary to start and create some dump >> files. >> > > pg_upgrade needs to access the old data AND all the tablespaces at the > same paths as the old server sees them AND the new data and tablespaces at > the same path as the NEW server sees them. if the two servers are in > different jails, I don't see how you could make that work... if you run > pg_upgrade in the host system, then all the paths are different for both > sets of data and tablespaces. > > I understand that it will need to access the old data and new data data as it sees it, but it is seeing everything as /usr/local/pgsql/data. Now lets say I have both versions 9.0 and 9.3 installed in the same jail. They will both need to use /usr/local/pgsql/data to access the physical data. But that will not work because all of the Postgres related files are in there, so you can only have 9.0 OR 9.3 use the /usr/local/pgsql/data directory. > > > > -- > john r pierce 37N 122W > somewhere on the middle of the left coast > > > > -- > 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] pg_upgrade & tablespaces
On Thu, Dec 19, 2013 at 4:16 PM, Adrian Klaver wrote: > On 12/19/2013 01:06 PM, Joseph Kregloh wrote: > >> It's easier to keep things segregated. It is not anymore different than >> doing the upgrade in the same jail. Which at the end of the day you are >> doing the upgrade in the same jail, because at the end of the day >> pg_upgrade just needs the old data an binary to start and create some >> dump files. >> >> But the real problem here is with the table spaces. Because in order to >> copy the relation over I would need to mount the old data to the >> /usr/local/pgsql/data on the new jail. The relation would be there and >> would finish successfully(I did this exercise). However the 9.3 install >> would be in a different directory, say /usr/local/pgsql_93 and will not >> have the data files because they now live in the old install location. >> > > Not sure all of this but I do have this question: > > In your original post you have: > > pg_upgrade -b /home/jkregloh/pg_bin/ -B /usr/local/bin/ > -d /home/jkregloh/pg_data/ -D /usr/local/pgsql/data/ -p 5452 -P 5451 -c > > Note: -d /home/jkregloh/pg_data/ > > In your mount info you have: > > /dev_db/stop_db/postgres_data on /usr/jails/postgres-93- > upgrade/home/jkregloh/pg_data/data (nullfs, local) > > If I am following correctly should it not be: > > -d /home/jkregloh/pg_data/data > Yes, you are correct. That's a typo on my part from copy/pasting earlier. > > >> -Joseph >> >> >> > > > -- > Adrian Klaver > adrian.kla...@gmail.com >
Re: [GENERAL] pg_upgrade & tablespaces
On Thu, Dec 19, 2013 at 4:18 PM, Bruce Momjian wrote: > On Thu, Dec 19, 2013 at 01:08:18PM -0800, Sergey Konoplev wrote: > > On Thu, Dec 19, 2013 at 12:49 PM, Joseph Kregloh > > wrote: > > > On Thu, Dec 19, 2013 at 3:46 PM, Sergey Konoplev > wrote: > > >> On Thu, Dec 19, 2013 at 12:27 PM, Joseph Kregloh > > >> wrote: > > >> > So what I get from this is that it does create the correct 9.3 > files in > > >> > the > > >> > new location, however it cannot copy the relation over because the > old > > >> > data > > >> > is in the /home/jkregloh/pg_data/data/drupal_dbspace/ not in > > >> > /usr/local/pgsql/data/drupal_dbspace/ as the pg_upgrade query tries > to > > >> > do. > > >> > > >> Can you show what ls -l /home/jkregloh/pg_data/data/pg_tblspc/ prints, > > >> please? > > >> > > > [pgsql@postgres-93-upgrade ~]$ ls -l > /home/jkregloh/pg_data/data/pg_tblspc/ > > > lrwxr-xr-x 1 pgsql pgsql41 Dec 19 19:53 11047389 -> > > > /home/jkregloh/pg_data/data/stats_dbspace > > > lrwxr-xr-x 1 pgsql pgsql44 Dec 19 19:53 11047390 -> > > > /home/jkregloh/pg_data/data/stats_indexspace > > > lrwxr-xr-x 1 pgsql pgsql49 Dec 19 19:53 11047391 -> > > > /home/jkregloh/pg_data/data/stats_staging_dbspace > > > > Bruce, may be it's a silly question, but the above makes me think so. > > I always keep tablespaces in locations different from the main data > > dir, and never faced something like this. > > > > Doesn't pg_upgrade do a stright replace of -d dir with -D dir > > everywhere in paths? > > pg_upgrade is looking at the data dir, the database oid, and relfilenode > to get the old path, and does the same for the new path. Tablespaces > point to the same location in old and new clusters --- only a > subdirectory PG_VERISON is different. > > Is /home/jkregloh/pg_data/data also your default cluster directory? If > so, having tablespaces inside of there will not work well as they will > continue to be stored in the old cluster's data directory. Those will > not be renamed/relocated by pg_upgrade. > > No, that is not my default cluster dir. That is just the data directory of my 9.0 install that I mounted there in order to do the pg_upgrade. Essentially that points to /usr/local/pgsql/data on my 9.0 jail. > -- > Bruce Momjian http://momjian.us > EnterpriseDB http://enterprisedb.com > > + Everyone has their own god. + >
Re: [GENERAL] pg_upgrade & tablespaces
On 12/19/2013 01:50 PM, Joseph Kregloh wrote: On Thu, Dec 19, 2013 at 4:14 PM, John R Pierce mailto:pie...@hogranch.com>> wrote: On 12/19/2013 1:06 PM, Joseph Kregloh wrote: It's easier to keep things segregated. It is not anymore different than doing the upgrade in the same jail. Which at the end of the day you are doing the upgrade in the same jail, because at the end of the day pg_upgrade just needs the old data an binary to start and create some dump files. pg_upgrade needs to access the old data AND all the tablespaces at the same paths as the old server sees them AND the new data and tablespaces at the same path as the NEW server sees them. if the two servers are in different jails, I don't see how you could make that work... if you run pg_upgrade in the host system, then all the paths are different for both sets of data and tablespaces. I understand that it will need to access the old data and new data data as it sees it, but it is seeing everything as /usr/local/pgsql/data. Now lets say I have both versions 9.0 and 9.3 installed in the same jail. They will both need to use /usr/local/pgsql/data to access the physical data. But that will not work because all of the Postgres related files are in there, so you can only have 9.0 OR 9.3 use the /usr/local/pgsql/data directory. No, that is not the case. The data directory can be different for different instances, it is a configure option. In fact the pg_upgrade docs point that out: http://www.postgresql.org/docs/9.3/interactive/pgupgrade.html See: Usage Steps 1-3 -- Adrian Klaver adrian.kla...@gmail.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] Multi Master Replication
On Thu, Dec 19, 2013 at 11:18 PM, Chris Travers wrote: > > > > On Thu, Dec 19, 2013 at 6:52 AM, Wolfgang Keller wrote: >> >> > 2. With sync replication, you have coordination problems and >> > therefore it is never (at least IME) a win compared to master-slave >> > replication since all writes must occur in the same order in the set, >> > or you need global sequences, or such. > I am not quite sure what the point is. I am not sure you will get the same > write extensibility if you list every table as replicated instead of > partitioned. What Postgres-XC gives you ideally is a no-storage and > multi-master coordination layer on top of master-slave data nodes. Some > things may need to be replicated multi-master between data nodes but that's > not a win write throughput-wise. You'd kill the write scalability of the application by marking all the tables as replicated. The communication between nodes uses SQL strings, so a DML on a replicated table needs to occur on all the nodes, and on top of that you need 2PC for a transaction commit if more than 2 nodes are involved in write operations in this transaction. > I am btw a reasonable fan of Postgres-XC within its problem domain, but it > is not a synchronous multi-master replication solution as far as write > scaling goes. OLTP applications that have a schema tunable for replication/partition to maximize join pushdown might be a good definition of the application range that could benefit from XC. > My point still holds, which is that synchronous multi-master replication > will never beat master-slave in write throughput. My understanding of > Postgres-XC is that you'd mark tables as replicated (instead of partitioned) > when they are going to be joined against by different nodes and infrequently > updated (and hence the write overhead is less of a problem than the > cross-node join overhead). Yep, exactly. Those tables are actually master tables and the point is to maximize the number of join clause push down to minimize the amount of data exchanged between the nodes because of the shared-nothing infrastructure. The type of tables that should be marked as partitioned is the once that keep growing and need to scale of the type "user" tables. This is actually how DBT-1 has been tuned when doing scaling testing with it: partition user and adress tables, replicate stock and item tables. Regards, -- Michael -- 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] Multi Master Replication
On Fri, Dec 20, 2013 at 8:48 AM, Michael Paquier wrote: > On Thu, Dec 19, 2013 at 11:18 PM, Chris Travers > wrote: >> >> >> >> On Thu, Dec 19, 2013 at 6:52 AM, Wolfgang Keller wrote: >>> >>> > 2. With sync replication, you have coordination problems and >>> > therefore it is never (at least IME) a win compared to master-slave >>> > replication since all writes must occur in the same order in the set, >>> > or you need global sequences, or such. >> I am not quite sure what the point is. I am not sure you will get the same >> write extensibility if you list every table as replicated instead of >> partitioned. What Postgres-XC gives you ideally is a no-storage and >> multi-master coordination layer on top of master-slave data nodes. Some >> things may need to be replicated multi-master between data nodes but that's >> not a win write throughput-wise. > You'd kill the write scalability of the application by marking all the > tables as replicated. The communication between nodes uses SQL > strings, so a DML on a replicated table needs to occur on all the > nodes, and on top of that you need 2PC for a transaction commit if > more than 2 nodes are involved in write operations in this > transaction. > >> I am btw a reasonable fan of Postgres-XC within its problem domain, but it >> is not a synchronous multi-master replication solution as far as write >> scaling goes. > OLTP applications that have a schema tunable for replication/partition > to maximize join pushdown might be a good definition of the > application range that could benefit from XC. > >> My point still holds, which is that synchronous multi-master replication >> will never beat master-slave in write throughput. My understanding of >> Postgres-XC is that you'd mark tables as replicated (instead of partitioned) >> when they are going to be joined against by different nodes and infrequently >> updated (and hence the write overhead is less of a problem than the >> cross-node join overhead). > Yep, exactly. Those tables are actually master tables and the point is > to maximize the number of join clause push down to minimize the amount > of data exchanged between the nodes because of the shared-nothing > infrastructure. The type of tables that should be marked as > partitioned is the once that keep growing and need to scale of the > type "user" tables. This is actually how DBT-1 has been tuned when > doing scaling testing with it: partition user and adress tables, > replicate stock and item tables. I actually wrote something stupid here, stock is partitioned and it makes sense as it faces lot of updates: http://images.wikia.com/postgresxc/images/6/66/PG-XC_Architecture.pdf (page 23) Thanks to Chris for pointing that out. -- Michael -- 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] Multi Master Replication
14 replies so far, and the OP hasn't chimed in with any feedback as to what their presumed requirements are based on. *meh* -- 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] Is it advisable to pg_upgrade directly from 9.0 to 9.3?
On Thu, Dec 19, 2013 at 10:07 PM, Laurentius Purba wrote: > Hi Greg, > > I just wanted to know if you were able successfully upgrading from 9.0 to > 9.3. > > I have been doing this upgrading this past week, but always ended up with > unsuccessful upgrade. > > It will be great if you can share you knowledge on this. It would be even better if you use this thread to report the problems you are seeing, such as someone could help you going through this upgrade process. Regards, -- Michael -- 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] Best way to sync possibly corrupted data?
On Fri, Dec 20, 2013 at 5:28 AM, Anand Kumar, Karthik wrote: > HI, > > We have an issue with possibly corrupt data in our postgresql server. Errors > like: > > ERROR: index "photos_p00_n2" contains unexpected zero page at block 0 > ERROR: invalid page header in block 12707 of relation > pg_tblspc/5020557/PG_9.1_201105231/16393/9014673 > > Thanks to all the suggestions from this list. We are in the process of > moving our database out to a different server, and we'll then set > zero_dameged_pages to on, run a full vacuum and reindex. > > The question I have is – what is the best method to transfer the data over > to ensure we don't copy over bad/corrupt data? I would think a filesystem > based copy (rsync, etc) should be avoided, and a pg_dump with a new initdb > is best? You should go with pg_dump if you are able to get a clean dump. Such block errors happen because of hardware issues, so you are not safe from additional failures that might happen while you do a copy of the existing data folder to a new system. Regards, -- Michael -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
