Re: [ADMIN] I want to import a database from a customer
Hi Josef, i must analyze a customer's database offline. Can i import the databse of the customer into our PostgreSQL environment ? What must the customer send us ? Whats the doings for us to open it in our envioronment ? use pg_dump -o -b -Fc [database to export] > [backup file] in your customer's side (man pg_dump for details) create the database where you'll import the backup and use pg_restore -v -O -d [database] [backup file] (man pg_restore for details) -- Arnau ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [ADMIN] Replication
Hi, [ please keep CCing to the list (reply all), as this certainly isn't a personal discussion and could help others. ] anorganic anorganic wrote: my opinion: partitioning is vertical and horizontal, dived table into two or more parts. > replicated only part of some object,here table = replicated two columns ins't partitioning, because table is same ;) and have one part = self table ;) The main point is, you divide data *somehow* into partitions and grant write rights to only *one* server for each partition, thus allowing you to use a simpler master-slave replication. i want have s1 and s2 tableA col1 col2 col3 tableA is on s1 and s2 same design s1 change only col1, s2 change only col3 is possible set replication on s1 only for col1 of tableA is possible set replication on s2 only for col3 of tableA this is not partitioning i think How else would you call it? In what way is it different from what's commonly known as vertical partitioning? Couldn't you do what you want with the help of ordinary vertical partitioning and using a VIEW viewA with all the columns col1, col2, col3? ok, sync is not saying something about when is transaction log send... Uhm.. yes it does. It has to be sent before commit confirmation is sent to the client (Postgres-R does some optimization here, but basically the above statement still holds true). It is the async approach, which doesn't say anything about when changes are sent to other servers. sync said: i coimmit transaction when all slaves send me commit :) asynch: i make change i commit this change and i send it to my slave. and i want do it fast as possible ;) No matter *how* fast you do that, conflicts can arise if you don't prevent them somehow. The requirement to send changes as fast as possible in async replication rings my alarm bell, because it sounds like your application cannot cope with conflicts and wants to prevent them by minimizing the propagation delays. That's prone to race conditions and probably won't work. However, if you partition your data vertically and do per-table master-slave replication, you of course don't have to worry about conflicts, as there is only exactly one master. Regards Markus ---(end of broadcast)--- TIP 6: explain analyze is your friend
[ADMIN] Recovering a deleted database problem
Earlier this evening I made the usual mistake someone makes at some point in their lives - and dropped a database thinking I didn't need it, then realised later I did. So, because I have DDL statement logging turned on, I could find the exact time/date it happened, and attempted to restore from my file-system level backup taken at 2am this morning, and rolled forward all my WAL logs archived throughout the day (98 files.) In the recovery.conf, I specified the date/time from the log file that the database was dropped and set recovery_target_inclusive so it would not include this transaction. However the restore has finished, and PostgreSQL thinks the database is there, but the relevant data directory in "base" is missing - so it's removed the file-system objects but not the system database entry. I've checked the base backup, and this directory is in the backup, hence it has been removed at some point during the restore. What I'm going to do now is to set the recovery target to about a minute earlier to make sure the transaction has not started when the recovery finishes - but I'm just asking if I'm missing something obvious, as this is the first time I've done a restore from WAL logs. (Note, after writing this, I tried restoring to a minute earlier (ie. 18:57:40) and still have the same problem. As a quick fix, I copied the base/35290 directory from the backup before I had run the recovery - does anyone know any caveats to doing this, as the DB seems to be working OK?) My recovery.conf is: # PostgreSQL database recovery config file restore_command = 'cp /path/to/wal/archive/%f "%p"' recovery_target_time = '"2007-01-04 18:58:40 -00:00"' recovery_target_inclusive = 'false' The log entry where I discovered the date/time is: 2007-01-04 18:58:40 GMT 84.45.66.158 postgres postgresql - LOG: statement: DROP DATABASE [dbname]; The error I get when I try to connect to [dbname] after the restore is: FATAL: database "[dbname]" does not exist DETAIL: The database subdirectory "base/35290" is missing. But the [dbname] database is still in the system catalogues: /usr/local/pgsql/bin/psql -U postgresql -d postgres -c "select datname from pg_database;" datname postgres [db1] template1 template0 [dbname] [db2] [db3] [db4] (8 rows) This is PostgreSQL 8.1.5 on FreeBSD 6.1. Many thanks, -- Andy Shellam NetServe Support Team the Mail Network "an alternative in a standardised world" ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[ADMIN] Connectionstring
How can I specify the "Encoding" in the connectionString using pgOleDB with Visual Basic.?
Re: [ADMIN] Connectionstring
Doing a quick Google search, it appears to be, you add "*Encoding*=UNICODE" or whatever you want your encoding to be, in your connection string. Try the pgsql-interfaces list - that's more appropriate for this sort of thing. Sistemas C.M.P. wrote: How can I specify the "Encoding" in the connectionString using pgOleDB with Visual Basic.? !DSPAM:37,459e5d08137101549039207! -- Andy Shellam NetServe Support Team the Mail Network "an alternative in a standardised world" ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [ADMIN] Connectionstring
Excuse the asterisks - they were added in by my mail client - it should be "Encoding=UNICODE" Andy Shellam (Mailing Lists) wrote: Doing a quick Google search, it appears to be, you add "*Encoding*=UNICODE" or whatever you want your encoding to be, in your connection string. Try the pgsql-interfaces list - that's more appropriate for this sort of thing. Sistemas C.M.P. wrote: How can I specify the "Encoding" in the connectionString using pgOleDB with Visual Basic.? -- Andy Shellam NetServe Support Team the Mail Network "an alternative in a standardised world" p: +44 (0) 121 288 0832/0839 m: +44 (0) 7818 000834 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [ADMIN] Recovering a deleted database problem
"Andy Shellam (Mailing Lists)" <[EMAIL PROTECTED]> writes: > (Note, after writing this, I tried restoring to a minute earlier (ie. > 18:57:40) and still have the same problem. The PITR recovery process in effect rolls forward until it finds a transaction-commit record >= the specified time. Now for normal database operations, stopping just short of the commit of the transaction is enough to ensure that the transaction has no effect. But for the XLOG_DBASE_DROP record, not so --- replaying that means "rm -rf base/whatever". So you've got to make sure the replay stops before it reaches that record, and that means you need a stop time <= the commit time of some *prior* transaction. I suppose this was a slow time of day and you didn't have any other commits in the prior minute :-( ... so take another look in the log and see what was the last commit before that, and use that time. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [ADMIN] Connectionstring
Hmm OK was worth a shot - probably best bet would be to ask on pgsql-interfaces. Andy. Sistemas C.M.P. wrote: With or without asterisks it doesn't work. This string work on ODBC but not with pgOLEDB - Original Message - From: "Andy Shellam (Mailing Lists)" <[EMAIL PROTECTED]> To: Sent: Friday, January 05, 2007 11:24 AM Subject: Re: [ADMIN] Connectionstring Excuse the asterisks - they were added in by my mail client - it should be "Encoding=UNICODE" Andy Shellam (Mailing Lists) wrote: Doing a quick Google search, it appears to be, you add "*Encoding*=UNICODE" or whatever you want your encoding to be, in your connection string. Try the pgsql-interfaces list - that's more appropriate for this sort of thing. Sistemas C.M.P. wrote: How can I specify the "Encoding" in the connectionString using pgOleDB with Visual Basic.? -- Andy Shellam NetServe Support Team the Mail Network "an alternative in a standardised world" p: +44 (0) 121 288 0832/0839 m: +44 (0) 7818 000834 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.5.432 / Virus Database: 268.16.6/617 - Release Date: 05/01/2007 11:11 a.m. !DSPAM:37,459e6166137101868784367! -- Andy Shellam NetServe Support Team the Mail Network "an alternative in a standardised world" p: +44 (0) 121 288 0832/0839 m: +44 (0) 7818 000834 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [ADMIN] Recovering a deleted database problem
Thanks for the info Tom, too much data will have been entered into the other databases in the cluster by now so I cannot give it another shot on that server, plus all of yesterday's WAL logs will have been purged by now by the daily backup routine. Is it enough to simply have re-copied in the base/xxx directory from the base backup, after the PITR recovery had completed (obviously any changes made to that database since the base backup won't have been restored but thankfully it's backed up nightly and doesn't change too often :-) ) All CRUD operations seem to be working on that database OK and the app that (I now know) uses it hasn't complained. What I'll probably do is try to simulate the same process again on a different machine to get myself a bit more familiar. Is there any other situations you can think of where this may also be relevant, or is it just when dropping a complete database? Many thanks, Andy. Tom Lane wrote: "Andy Shellam (Mailing Lists)" <[EMAIL PROTECTED]> writes: (Note, after writing this, I tried restoring to a minute earlier (ie. 18:57:40) and still have the same problem. The PITR recovery process in effect rolls forward until it finds a transaction-commit record >= the specified time. Now for normal database operations, stopping just short of the commit of the transaction is enough to ensure that the transaction has no effect. But for the XLOG_DBASE_DROP record, not so --- replaying that means "rm -rf base/whatever". So you've got to make sure the replay stops before it reaches that record, and that means you need a stop time <= the commit time of some *prior* transaction. I suppose this was a slow time of day and you didn't have any other commits in the prior minute :-( ... so take another look in the log and see what was the last commit before that, and use that time. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster !DSPAM:37,459e6a32137101648020742! -- Andy Shellam NetServe Support Team the Mail Network "an alternative in a standardised world" ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [ADMIN] Recovering a deleted database problem
"Andy Shellam (Mailing Lists)" <[EMAIL PROTECTED]> writes: > Is it enough to simply have re-copied in the base/xxx directory from the > base backup, after the PITR recovery had completed (obviously any > changes made to that database since the base backup won't have been > restored but thankfully it's backed up nightly and doesn't change too > often :-) ) Well, I'd be a little worried about whether the base backup was self-consistent, but if it was taken at a time where the DB was idle then you can probably get away with this. > What I'll probably do is try to simulate the same process again on a > different machine to get myself a bit more familiar. Is there any other > situations you can think of where this may also be relevant, or is it > just when dropping a complete database? AFAIK the only operations that have non-rollbackable side effects are CREATE/DROP DATABASE and CREATE/DROP TABLESPACE. For any of these, you'd end up with inconsistent state if you try to stop replay just before the commit record. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[ADMIN] Can't See Data - Plz Help!
Hi List! I'm really in need of some guidance here.. We're running PostgreSQL 8.0 and I have PGAdmin v.1.4.3 on my local pc and version 1.2.2 on my server & the other developer's pc - when I open PGAdmin to connect to the database(s), I can do so without any problems, however, when we go to view the data in the database(s), we cannot see anything, the window opens with the menu bars, but there are no column names, and no data. And if I try a 'Refresh', it appears to do something, but still nothing is displayed. If I use a command prompt and connect to the db's, I can select from the tables and everything returns OK; and using our program, which connects to the db's using JDBC, it's connecting and returning data OK... However, not being able to view the data in the tables and views is an issue in our development and testing (not to mention sanity). It's happening on different databases, on multiple pc's, with different versions of the Admin tool. We haven't done any updates to either the database, our version of Postgres, or the Admin tool any thoughts??? Thanks in advance for your time and help! -Jeanna ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[ADMIN] Problems restoring big tables
Hi all, I have to restore a database that its dump using custom format (-Fc) takes about 2.3GB. To speed the restore first I have restored everything except (played with pg_restore -l) the contents of some tables that's where most of the data is stored. This server is a debian running PostgreSQL 8.1.4. When I try to restore these table's contents I've got an error: $ time pg_restore -v -d espsm_asme -O -L espsm_asme_components_statistics_data.list espsm_asme-20070105-0619.custom pg_restore: connecting to database for restore pg_restore: implied data-only restore pg_restore: restoring data for table "statistics_operators" pg_restore: ERROR: out of memory DETAIL: Failed on request of size 32. CONTEXT: COPY statistics_operators, line 25663678: "137320348 58618027 20060804220356-1-93-3096\N 2006-08-04 22:03:56+02 1 34675522993 5755 71 1 6 \N" pg_restore: [archiver (db)] error returned by PQendcopy: ERROR: out of memory DETAIL: Failed on request of size 32. CONTEXT: COPY statistics_operators, line 25663678: "137320348 58618027 20060804220356-1-93-3096\N 2006-08-04 22:03:56+02 1 34675522993 5755 71 1 6 \N" pg_restore: *** aborted because of error real23m16.490s user1m55.203s sys 0m5.672s I don't know how I can solve this. This server has 4GB of RAM plenty of space in the disks. [EMAIL PROTECTED]:~/asme_restore$ df -h FilesystemSize Used Avail Use% Mounted on /dev/sda1 28G 2.8G 24G 11% / tmpfs 2.0G 0 2.0G 0% /dev/shm /dev/sdb1 34G 161M 32G 1% /dblog /dev/sdc1 135G 6.2G 122G 5% /srv tmpfs 10M 44K 10M 1% /dev [EMAIL PROTECTED]:~/asme_restore$ cat /proc/sys/kernel/shmmax 16384 Any idea about how to fix this? Regards -- Arnau ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [ADMIN] Recovering a deleted database problem
Well, I'd be a little worried about whether the base backup was self-consistent, but if it was taken at a time where the DB was idle then you can probably get away with this. It gets backed up at 2am in the morning and AFAIK there'd be very few (if any) transactions going through until about 5am, so it should be OK. AFAIK the only operations that have non-rollbackable side effects are CREATE/DROP DATABASE and CREATE/DROP TABLESPACE. For any of these, you'd end up with inconsistent state if you try to stop replay just before the commit record. OK thanks for that Tom, after glancing through the online documentation, I've enabled logging of checkpoints etc. by setting "log_min_messages" to "log" - is this enough to log the times that transactions are committed, so I can find this info should I need it again in the future? Thanks, -- Andy Shellam NetServe Support Team the Mail Network "an alternative in a standardised world" ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [ADMIN] Can't See Data - Plz Help!
Hi Jeanna, Does pgAdmin give you back any error, like permission denied, or anything like that? Can you see all the properties of the table, such as indexes, tables etc before you open it? As it's happening on various PCs and versions of pgAdmin, I'd hazard a guess that it's server-side, but I'm not sure. Also have you tried any other client tools? EMS do a good PGSQL Manager for free (the "Lite" version) - and you could use that to determine if the problem is with the server or the client application, a different tool may also highlight an error that pgAdmin does not. EMS is at www.sqlmanager.net. Might be worth asking on pgadmin-support@postgresql.org as the developers of pgAdmin can have a look-see too and might be able to suggest other ideas. Regards, Andy. Jeanna Geier wrote: Hi List! I'm really in need of some guidance here.. We're running PostgreSQL 8.0 and I have PGAdmin v.1.4.3 on my local pc and version 1.2.2 on my server & the other developer's pc - when I open PGAdmin to connect to the database(s), I can do so without any problems, however, when we go to view the data in the database(s), we cannot see anything, the window opens with the menu bars, but there are no column names, and no data. And if I try a 'Refresh', it appears to do something, but still nothing is displayed. If I use a command prompt and connect to the db's, I can select from the tables and everything returns OK; and using our program, which connects to the db's using JDBC, it's connecting and returning data OK... However, not being able to view the data in the tables and views is an issue in our development and testing (not to mention sanity). It's happening on different databases, on multiple pc's, with different versions of the Admin tool. We haven't done any updates to either the database, our version of Postgres, or the Admin tool any thoughts??? Thanks in advance for your time and help! -Jeanna ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster !DSPAM:37,459e7bd3137101637590987! -- Andy Shellam NetServe Support Team the Mail Network "an alternative in a standardised world" p: +44 (0) 121 288 0832/0839 m: +44 (0) 7818 000834 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[ADMIN] PITR recovery
Maybe this is answered somewhere or maybe self-evident, but I just wanted to make sure. I want to know if it's possible to do PITR between different platforms. I can try and learn, but if anyone knows, I'd appreciate it. 1. file formats What is the chance that the file format of files under "data" is platform independent? I.e. would it be possible to restore the file system backup and use the PITR method from a Solaris/SPARC main to a Linux backup, using their respective native file system? What are the minimal conditions to be met? 2. sql dump and PITR Is it possible to use the PITR method with SQL dump? (pg_start_backup -> sql dump -> pg_stop_backup) I guess not, but just want to make sure. Thanks. Ben K. Developer http://benix.tamu.edu ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [ADMIN] Can't See Data - Plz Help!
Thanks for the reply, Andy. No, no error from pgadmin, and, yes, I can see all the properties of the tables before opening it. You can open the tables and see menu bars and what-not, just no data in the tables/views, but like I said, I know the data is in there, because I can access it using psql from the command line. We haven't tried any other client tools, but I'll give that a try, thanks. And, if that doesn't help, I'll take your suggestion of asking on pgadmin-support. Thanks again!! -Jeanna -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Andy Shellam (Mailing Lists) Sent: Friday, January 05, 2007 12:52 PM To: Jeanna Geier Cc: Pgsql-Admin@Postgresql.Org Subject: Re: [ADMIN] Can't See Data - Plz Help! Hi Jeanna, Does pgAdmin give you back any error, like permission denied, or anything like that? Can you see all the properties of the table, such as indexes, tables etc before you open it? As it's happening on various PCs and versions of pgAdmin, I'd hazard a guess that it's server-side, but I'm not sure. Also have you tried any other client tools? EMS do a good PGSQL Manager for free (the "Lite" version) - and you could use that to determine if the problem is with the server or the client application, a different tool may also highlight an error that pgAdmin does not. EMS is at www.sqlmanager.net. Might be worth asking on pgadmin-support@postgresql.org as the developers of pgAdmin can have a look-see too and might be able to suggest other ideas. Regards, Andy. Jeanna Geier wrote: > Hi List! I'm really in need of some guidance here.. > > We're running PostgreSQL 8.0 and I have PGAdmin v.1.4.3 on my local pc and > version 1.2.2 on my server & the other developer's pc - when I open PGAdmin > to connect to the database(s), I can do so without any problems, however, > when we go to view the data in the database(s), we cannot see anything, the > window opens with the menu bars, but there are no column names, and no data. > And if I try a 'Refresh', it appears to do something, but still nothing is > displayed. > > If I use a command prompt and connect to the db's, I can select from the > tables and everything returns OK; and using our program, which connects to > the db's using JDBC, it's connecting and returning data OK... > > However, not being able to view the data in the tables and views is an issue > in our development and testing (not to mention sanity). It's happening on > different databases, on multiple pc's, with different versions of the Admin > tool. We haven't done any updates to either the database, our version of > Postgres, or the Admin tool any thoughts??? > > Thanks in advance for your time and help! > -Jeanna > > > ---(end of broadcast)--- > TIP 2: Don't 'kill -9' the postmaster > > !DSPAM:37,459e7bd3137101637590987! > > > -- Andy Shellam NetServe Support Team the Mail Network "an alternative in a standardised world" p: +44 (0) 121 288 0832/0839 m: +44 (0) 7818 000834 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [ADMIN] Can't See Data - Plz Help!
One other thing I've just thought of, if you issue a manual query from within pgAdmin - does this succeed? Also roughly how big are the tables (i.e. number of rows) - does it help if you set a LIMIT in the SQL clause (by default I think it's 1000 rows but try setting a LIMIT of 1 row and see if that comes back.) Andy. Jeanna Geier wrote: Thanks for the reply, Andy. No, no error from pgadmin, and, yes, I can see all the properties of the tables before opening it. You can open the tables and see menu bars and what-not, just no data in the tables/views, but like I said, I know the data is in there, because I can access it using psql from the command line. We haven't tried any other client tools, but I'll give that a try, thanks. And, if that doesn't help, I'll take your suggestion of asking on pgadmin-support. Thanks again!! -Jeanna ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [ADMIN] PITR recovery
On Fri, Jan 05, 2007 at 12:59:51 -0600, "Ben K." <[EMAIL PROTECTED]> wrote: > > Maybe this is answered somewhere or maybe self-evident, but I just wanted > to make sure. I want to know if it's possible to do PITR between different > platforms. I can try and learn, but if anyone knows, I'd appreciate it. > > > 1. file formats > > What is the chance that the file format of files under "data" is platform > independent? I.e. would it be possible to restore the file system backup > and use the PITR method from a Solaris/SPARC main to a Linux backup, using > their respective native file system? What are the minimal conditions to be > met? No the data is not only dependent on the platform, but also on the configure options used for the build. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[ADMIN] vacuum fails with 'invalid page header' message
We had a vacuum fail recently with the following error: invalid page header in block 846 of relation "move_pkey" Anyone have an idea what could cause this problem and what we need to do to resolve it? Running on Red Hat Enterprise 3, postgres 7.4.13 -- Until later, Geoffrey Those who would give up essential Liberty, to purchase a little temporary Safety, deserve neither Liberty nor Safety. - Benjamin Franklin ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [ADMIN] vacuum fails with 'invalid page header' message
Geoffrey wrote: We had a vacuum fail recently with the following error: invalid page header in block 846 of relation "move_pkey" Anyone have an idea what could cause this problem and what we need to do to resolve it? Running on Red Hat Enterprise 3, postgres 7.4.13 Regarding the issue above, is it possible that re-indexing the database could resolve the problem, since the error is related to an index. move_pkey is an index on field recid. -- Until later, Geoffrey Those who would give up essential Liberty, to purchase a little temporary Safety, deserve neither Liberty nor Safety. - Benjamin Franklin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [ADMIN] vacuum fails with 'invalid page header' message
On Fri, 2007-01-05 at 16:19, Geoffrey wrote: > Geoffrey wrote: > > We had a vacuum fail recently with the following error: > > > > invalid page header in block 846 of relation "move_pkey" > > > > Anyone have an idea what could cause this problem and what we need to do > > to resolve it? > > > > Running on Red Hat Enterprise 3, postgres 7.4.13 > > Regarding the issue above, is it possible that re-indexing the database > could resolve the problem, since the error is related to an index. > move_pkey is an index on field recid. Yes, reindex might fix it. But more than likely there's a problem with your hardware somewhere, and it needs to be checked out carefully for problems. i.e. bad hard drive, memory, cpu, etc... ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [ADMIN] postgres 8.2 transaction id wraparound
On Jan 2, 2007, at 6:09 PM, Sriram Dandapani wrote: I read the release notes for 8.2 which mentioned that transaction id wraparounds are now on a per-table basis versus database-wide. Currently for 8.1 I issue a vacuumdb –a command which takes a coule of days due to the size of the databse. What is the equivalent command in 8.2 (assuming autovacuum is turned off) vacuumdb -a will still work, though you might do better to let autovacuum take care of things since it will only vacuum tables that need to be vacuumed. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [ADMIN] Can't See Data - Plz Help!
"Jeanna Geier" <[EMAIL PROTECTED]> writes: > We're running PostgreSQL 8.0 and I have PGAdmin v.1.4.3 on my local pc and > version 1.2.2 on my server & the other developer's pc - when I open PGAdmin > to connect to the database(s), I can do so without any problems, however, > when we go to view the data in the database(s), we cannot see anything, Sounds like a pgAdmin-specific issue. You could probably get more knowledgeable help on the pgAdmin list --- see http://www.pgadmin.org/support/ regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [ADMIN] Problems restoring big tables
Arnau <[EMAIL PROTECTED]> writes: >I have to restore a database that its dump using custom format (-Fc) > takes about 2.3GB. To speed the restore first I have restored everything > except (played with pg_restore -l) the contents of some tables that's > where most of the data is stored. I think you've outsmarted yourself by creating indexes and foreign keys before loading the data. That's *not* the way to make it faster. > pg_restore: ERROR: out of memory > DETAIL: Failed on request of size 32. > CONTEXT: COPY statistics_operators, line 25663678: "137320348 58618027 I'm betting you ran out of memory for deferred-trigger event records. It's best to load the data and then establish foreign keys ... indexes too. See http://www.postgresql.org/docs/8.2/static/populate.html for some of the underlying theory. (Note that pg_dump/pg_restore gets most of this stuff right already; it's unlikely that you will improve matters by manually fiddling with the load order. Instead, think about increasing maintenance_work_mem and checkpoint_segments, which pg_restore doesn't risk fooling with.) regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
