On Tue, Mar 30, 2010 at 10:07:54PM -0400, Dai, Tino wrote:
> I'm not a dba. I'm a sysadmin by training. Is there some way to
> mirror the disks at the OS level? And then move it to the new
> machine. Just a though, I don't know the exact steps. But if you
> are interested,
Hi Renato,
dump/restore is the way to go. I suppose, you don't want to compile
Postgres for 32 bit on the new machine which might work and might allow
you to do the PITR migration.
And "downtime is not an option" is always a sign of insufficient
planning beforehand. There is no system which doesn
Hi Marc,
On Fri, Oct 23, 2009 at 03:52:16PM +0200, Marc Mamin wrote:
> > You might add pigz as a post-processing step and disabling compression in
> > pg_dump.
>
> The problem with this solution is that it make it necessary to
> decompress the dump entirely before using pg_restore (or did I mis
Hi Marc,
On Fri, Oct 23, 2009 at 02:48:58PM +0200, Marc Mamin wrote:
> I'm using pg_dump intensively, and until now using the plaintext format
> which allows me to pipe the output to pigz (http://zlib.net/pigz/)
> This is the fastest way I've found to generate compressed backups.
>
> (These back
Hi Dick,
On Wed, Aug 19, 2009 at 09:23:22PM +0200, Dick Visser wrote:
> A bit of a long first story, hope someone is able to help...
>
> We have got a Ubuntu 8.04 server running Postfix, which reads its
> mailing list subscription files, aliases, virtual, domains, etc from a
> Postgres database.
On Fri, Jul 10, 2009 at 08:49:40AM -0500, Kevin Grittner wrote:
> > So, what's a good value to set to effective_cache_size with 10 Gb
> > RAM?
>
> It depends on how many other things you have on the system which are
> using RAM, but probably something around 9GB would be appropriate.
> Maybe eve
Hi Rafael,
On Thu, Jul 09, 2009 at 07:18:55PM -0300, Rafael Domiciano wrote:
> Today, I've upgraded a dedicated postgres server, from 2 Gb to 10 Gb.
> Everything gone well.
>
> But, I would like shared buffers to use at least 5 Gb of the total memory.
> Setting kernel.shmmax with 6291456000 (600
On Fri, Jun 19, 2009 at 05:50:25PM +0800, Tony Liao wrote:
> Hi,All
>for example,I have a query as this
> select id,product_id from table_name where product_id in
> (6,3,4,10,7) order by .
>the results I want to get as bellow:
> idproduct_id.
Hi Igor,
On Mon, Jun 15, 2009 at 01:15:30PM -0700, Igor Polishchuk wrote:
> 2. We can kill the idle sessions periodically. This will free up a big chunk
> of memory already allocated to the sessions. The application will gradually
> reestablish the connections, and the new sessions will start wi
Hi Roland,
On Tue, Jun 09, 2009 at 09:31:43AM -0500, Roland Hughes wrote:
> > > While it shouldn't be much data, it is also non-robust. With robust
> > > databases, you can create as many databases as you want in as many places
> > > as you want. A "fresh install" means that you only have to "te
On Tue, Jun 09, 2009 at 09:10:20AM -0500, Roland Hughes wrote:
> On Tuesday 09 June 2009 08:55:18 am Tino Schwarze wrote:
> > On Tue, Jun 09, 2009 at 07:57:34AM -0500, Roland Hughes wrote:
> > > This has not been the case with 64-bit OpenSuSE. A fresh re-install of
>
(which shouldn't be much data).
Tino.
> On Tuesday 09 June 2009 04:50:07 am Tino Schwarze wrote:
> > Hi Roland,
> >
> > On Mon, Jun 08, 2009 at 12:23:06PM -0500, Roland Hughes wrote:
> > > I have had a question for some time and cannot seem to find an answ
Hi Roland,
On Mon, Jun 08, 2009 at 12:23:06PM -0500, Roland Hughes wrote:
> I have had a question for some time and cannot seem to find an answer.
>
> Is there a way to add pre-existing tablespace to a fresh Postgres install?
>
> Typically I create tablespace on some TB drives and place all dat
Hi pg043g9j2g (or what's your name?),
> For various reasons I had to abandon that OS install and
> reformat/reinstall WinXP. Time passes... Today I am looking to recover
> my work and basically resume where I left off.
>
> One problem is that I never did an "export" of the old database using
Hi Tom,
On Wed, Apr 01, 2009 at 08:58:54PM -0400, Tom Lane wrote:
> >> Maybe we should remove the code and make
> >> VACUUM FULL do the table-rewrite thing.
>
> > What do you mean with "the table-rewrite thing", exactly?
>
> Like CLUSTER, except not bothering to sort the rows: just seqscan the
On Wed, Apr 01, 2009 at 08:09:31PM -0400, Tom Lane wrote:
> > Nowadays, with many many new users, and no historical context, they do
> > just take it for "a better kind of vacuum" when in fact it is really
> > like reindex to indexes. I'd vote for rebuild [table]; as the new way
> > to spell vacu
Hi Rafael,
On Wed, Apr 01, 2009 at 06:51:00PM -0300, Rafael Domiciano wrote:
> > > I have some doubts about Vacuum Full. There We go:
> > > 1) The Only thing that Vacuum Full (Only Full, not Analyze) is to clean
> > > "dead space" on the disc, and reorganize the relation at the physical
> > level
On Tue, Mar 31, 2009 at 08:31:43AM -0300, Agustin Ignacio Genoves wrote:
> I guess you should use Bytea
Or go for large objects.
> > Greetings:
> >
> > I need to use a PostgreSQL Data Base to store different kinds of files, eg:
> > images, videos. So, I'm wondering what data type should I use.
>
Hi there,
I'm going to pg_restore a database dump of about 220 GiB (uncompressed,
but most data is BLOBs). The machine has 8 GiB of memory and 8 cores.
Is there any advice to speed up restoring, postgresql.conf-wise?
I already have a script which does the data loading and index creation
in parall
On Fri, Feb 13, 2009 at 03:13:28PM -0700, Scott Marlowe wrote:
[...]
> Yeah, that's pretty bad. ~2 Million live rows and ~18 Million dead
> ones is a pretty badly bloated table.
>
> Vacuum full is one way to reclaim that lost space. You can also dump
> and restore that one table, inside a drop
Hi Roger,
On Fri, Feb 13, 2009 at 01:56:32PM -0800, Roger Ging wrote:
>
Please don't post HTML mails to mailing lists. Thanks.
> I can only answer a couple of the questions at the moment. I had to
> kill the vacuum full and do a regular vacuum, so I can't get the iostat
> and vmstat outputs r
Hi Mike,
> I am newbie with PostgreSQL. I have separate table space for my
> databases, which is located in separate folder, which is different to
> default. I would like to carry out file system level backup. Do I need
> to backup only folder with my table space or all pg folders.
Do you kno
dresses = '*'
> in postgresql.conf should do it. I suppose, you forgot to restart
> postgresql (reload might not be enough).
>
> HTH,
>
> Tino.
>
> > --- On Fri, 5/12/08, Tino Schwarze wrote:
> > From: Tino Schwarze
> > Subject: Re: [ADMIN]
reload might not be enough).
HTH,
Tino.
> --- On Fri, 5/12/08, Tino Schwarze <[EMAIL PROTECTED]> wrote:
> From: Tino Schwarze <[EMAIL PROTECTED]>
> Subject: Re: [ADMIN] Remote access
> To: pgsql-admin@postgresql.org
> Date: Friday, 5 December, 2008, 12:53 AM
>
>
Hi,
> I am getting this error when I try to access the database in another computer
> within the same network.
>
> could not connect to server: Connection timed out (0x274C/10060). Is the
> server running on host "XX.XX.XX.XX" and accepting TCP/IP connections on port
> 5432?
>
[...]
> My
On Thu, Nov 27, 2008 at 09:13:04AM +0530, AmitKumar Jain wrote:
> I am using PostgreSQL 8.3 version . The autovacuum process is stucking
> my system as it automtically start even though its off is
> postgresql.conf.
>
> 2. Is there any benchmark tool for Autovacuum process or can we know how
On Wed, Nov 26, 2008 at 06:13:41PM -0700, Fabricio wrote:
> I have an IBM server with NUMA architecture, two nodes with 4 CPU quad
> core and 64 GB of RAM each, and PostgrSQL 8.3.5. Is there any way to
> avoid the performance degradation when the load goes up and used the
> two nodes? I understand
On Mon, Nov 10, 2008 at 10:54:57AM -0700, Scott Marlowe wrote:
> > Is it at all possible to get pgsql installed on a system without access to a
> > shell? A friend is wanting me to install a particular bulletin board system
> > on her website; however, her hosting plan only provides the MySQL dat
On Mon, Nov 10, 2008 at 09:50:09AM -0600, Dana Holland wrote:
> Is it at all possible to get pgsql installed on a system without access
> to a shell? A friend is wanting me to install a particular bulletin
> board system on her website; however, her hosting plan only provides the
> MySQL databa
Hi,
On Thu, Nov 06, 2008 at 04:15:02PM +0530, brahma tiwari wrote:
> My database server db01 is on linux environment and size of base folder
> increasing very fast unexpectedly(creating renamed files of 1 GB in base
> folder like 1667234568.10)
This sounds like your max_fsm_pages setting is to
On Mon, Sep 22, 2008 at 09:43:29AM -0400, Brian A. Seklecki wrote:
BTW: What version of PG are you using?
> We need to kill apache P-conn pools from a an application/web server
> cluster for a specific database. We use a load-balancer, so we shut
> down the HA L4 Service VIP that the web servers
On Fri, Aug 15, 2008 at 11:09:02AM +1000, [EMAIL PROTECTED] wrote:
> Nice, that has cleared it up.
>
> I am on 8.1 also.
>
> On my test box, a standard dump took 6m 26sec & a -Fc dump took 11min 2sec.
> That's not a great difference, but the size difference is quite noticeable.
You might use --c
On Fri, Jul 25, 2008 at 04:49:48PM +0530, neo3 matrix wrote:
> I have a table property_details having columns (mid,pid,pv,vt)
> Accidentally , i fired a query
> delete from property_details where mid=27617 and pid=20 or pid=21 or pid=22
> , which resulted in deletion of 418 rows which had pids 21
On Sat, Jul 19, 2008 at 04:29:00PM +0530, Kartik wrote:
> hello there,i am new to postgresql
> i am using postgresql 8.3.3 and i am writing one whole transaction. i want
> to know how to set auto commit off
> because if any error occours i want the whole transaction to be rolled back.
> when i trie
On Fri, Jul 18, 2008 at 04:29:42PM +0700, ria ulfa wrote:
> i want to copy table from file with command:
> COPY log FROM '/tmp/logfile.txt';
> but there is error:
> ERROR: could not open file "/tmp/logfile.txt" for reading: Permission
> denied
> what can i do for repair this error??
> tha
On Wed, Jul 09, 2008 at 06:23:10PM +0900, Eko Prasetiyo wrote:
> Now i have pgsql 8.0 server installed on CentOS Linux (Redhat).
> How to install pqsql 8.2 on different directory and port ?
> In Windows we can easily do it.
Just download the source and use for example
./configure --prefix=/usr/lo
On Mon, Jul 07, 2008 at 02:19:19PM -0400, Carol Walter wrote:
> I asked this question a while ago and I didn't get an answer.
> Perhaps I asked the question in a manner that wasn't clear. I
> upgraded from 8.1.3 to 8.2.3. I left the 8.1.3 database intact in
> case I had to revert to it.
On Fri, Jul 04, 2008 at 06:50:43AM -0700, Lennin Caro wrote:
> try this
>
> select pg_cancel_backend();
>
> if the command not work kill the process in os
>
> kill -9 proccessid_os
*NO*! Never kill -9 a postgres backend!!! This is like a headshot, no
chance to clean up. You will make your whole
On Thu, Jul 03, 2008 at 09:10:47PM +0200, Dimitri Fontaine wrote:
> >I've considered creating a tablespace in a directory owned by the
> >user , so I can use Linux quotas to prevent higher disk usage , but
> >this turned out be a bad thought, as all the files are anyway owned
> >by the postg
On Tue, Jul 01, 2008 at 03:12:39PM +0700, ria ulfa wrote:
> i use linux fedora 7 and database PostgreSQL 8.
> i want to copy table in postgre to a file.
> i used code : simps=# COPY log TO '/root/documents/log.sql';
> note= simps: name of database
> but there is error: could not open file "/
On Mon, Jun 30, 2008 at 04:01:14PM -0300, Rafael Domiciano wrote:
> The Postgres version is 8.3.3 and I am using Fedora Core 8.
> I have in the actual server around 70 connections the same time. I am
> assigning for this 100.
And what does the server do? Mainly SELECT / UPDATE / INSERT / DELETE,
h
On Mon, Jun 30, 2008 at 02:52:05PM -0300, Rafael Domiciano wrote:
> Folks, I am configuring a new Postgres Server, that's gonna substitute the
> critical server of the enterprise.
> I have a good machine:
> Quad-Core 2.5 Ghz
> 4 Gb RAM
> 1 Dedicated HD 300 Gb Sata for the PostgreSQL Directory
If
On Mon, Jun 30, 2008 at 03:15:34PM +0200, Thomas Bräutigam wrote:
> Why avoid RAID5? I though that would be good? Can you explain this?
RAID5 has a lot of disadvantages, especially in terms of performance.
.oO(There was a link posted recently...)
> Writing in the DB, I add about 5-10 GB of data
On Mon, Jun 30, 2008 at 02:30:23PM +0200, Thomas Bräutigam wrote:
> I have a pretty huge Postgres DB, about 1,3 to 1,5 Terra.
Thats plenty of data.
> What do you guys recommend on RAID Levels for this Database. Which
> does Postgres recommend, and with which do Postgres run very good or
> in th
On Mon, Jun 02, 2008 at 03:16:54PM -0700, [EMAIL PROTECTED] wrote:
> I'd like to use postgres to store my secret data in a way that only
> me (the DBA, owner of the table) can access the talbe while the root
> (system administrator) who installed and maintains the server - will
> not be able to s
On Wed, Jun 04, 2008 at 11:43:24AM +0200, Rudolf van der Leeden wrote:
> I'm currently testing a 25G Postgres 8.3.0 database on Apple Xserve/
> Intel with Mac OSX 10.5 (Leopard).
>
> There are two identical machines with identical configurations (hw
> and sw).
> Running a pg_restore of a binar
On Tue, May 13, 2008 at 01:18:24PM -0600, kevin kempter wrote:
> we seem to be getting deadlock quite a lot. We have a python process
> that does the following and we keep getting deadlocks. Is this a real
> deadlock or maybe a wait condition? Any help in debugging / resolving
> this would b
Hi James,
On Mon, May 12, 2008 at 09:25:34AM +0200, James Farrugia wrote:
> First of all thanks for the immediate replies!
> Was actually waiting for the right moment to upgrade to 8.3 but migrating a
> live 1Tb database is a bit daunting especially if you have never done it
> before (as in my ca
On Sun, May 11, 2008 at 09:29:01AM -0500, Potluri Srikanth wrote:
> can anyone provide me the security ACCESS matrix in prostgresql ?
Have a look at the fine manual:
http://www.postgresql.org/docs/8.3/static/privileges.html
HTH,
Tino.
--
"What we resist, persists." (Zen saying)
www.craniosa
On Wed, Apr 16, 2008 at 12:58:29PM +0300, Achilleas Mantzios wrote:
> > > Hi, i just started the process of migrating from 7.4 to 8.3.1.
> > > On restoring, apart from the easily explainable ERRORs (both in
> > > DDL, COPY), i got some ugly ERRORs denoting foreign key constraint
> > > violations.
On Wed, Apr 16, 2008 at 12:35:26PM +0300, Achilleas Mantzios wrote:
> Hi, i just started the process of migrating from 7.4 to 8.3.1.
> On restoring, apart from the easily explainable ERRORs (both in DDL, COPY), i
> got some ugly ERRORs
> denoting foreign key constraint violations.
> Since the dump
On Tue, Apr 08, 2008 at 04:52:23PM -0300, Sergio Gabriel Rodriguez wrote:
> I'm using postgresql 7.4, I have a problem with pg_dump/pg_restore. I
> want to back and restore only one schema from my db (I did it), but
> when I restore it, postgresql found errors in some views, this ones
> needs of o
On Tue, Apr 08, 2008 at 12:13:36PM +0200, Johann Spies wrote:
> > > This took about a week on a 2xCPU quadcore server with 8Gb RAM.
> >
> > This is not the most interesting thing here. What disk I/O subsystem do
> > you use? At least a hardware RAID controller with RAID 0 or 10 should
> > be us
On Tue, Apr 08, 2008 at 11:42:34AM +0200, Johann Spies wrote:
> 12501 ?S 0:00 /usr/lib/postgresql/8.1/bin/postmaster -D
> /var/lib/postgresql/8.1/main -c
> config_file=/etc/postgresql/8.1/main/postgresql.conf
> 12504 ?D 0:54 \_ postgres: writer process
> 12505 ?
On Tue, Apr 01, 2008 at 05:52:01PM +0200, Shavonne Marietta Wijesinghe wrote:
> Thanks Tino. But what do you mean by "netstat -tln" i use the pgadmin.
So you're on Windows? You didn't mention any operating system...
What does Postgres say on startup?
Tino.
--
„Es gibt keinen Weg zum Frieden. De
On Tue, Apr 01, 2008 at 04:44:29PM +0200, Shavonne Marietta Wijesinghe wrote:
> I have my postgresql(ver. 8 ) db on one computer (as localhost) and i'm
> trying to access it from another computer but i get the error
> "could not connect to Server: Connection refused"
>
> I followed the guide and
d there are possibly larger databases
around.
Bye,
Tino.
--
www.craniosacralzentrum.de
www.spiritualdesign-chemnitz.de
www.forteego.de
Tino Schwarze * Lortzingstraße 21 * 09119 Chemnitz
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
ecifying -N and -B on command line, I would set the
appropiate options in postgresql.conf - max_connections and
shared_buffers.
--
www.craniosacralzentrum.de
www.spiritualdesign-chemnitz.de
www.forteego.de
Tino Schwarze * Lortzingstraße 21 * 09119 Chemnitz
---(e
that database, and then use a client (PSQL) encoding of
> "LATIN1", and let PostgreSQL convert the data? Pros & cons?
I'd store anything UTF8. The performance penalty should be very tiny.
> I presume this ("admin") is the proper PostgreSQL list to post thi
n.
> To ease PostgreSQL newbies manual grasping, I certainly don't think it will
> need shortening the documentation.
Maybe there could be quickstart chapters or HOWTOs.
Bye,
Tino.
--
www.craniosacralzentrum.de
www.spiritualdesign-chemnitz.de
Tino Schwarze * Lortzingstraß
hen the connection was opened by looking at the process'
creation time.
HTH,
Tino.
--
www.craniosacralzentrum.de
www.spiritualdesign-chemnitz.de
Tino Schwarze * Lortzingstraße 21 * 09119 Chemnitz
---(end of broadcast)---
TIP 1: if posting/read
first query takes some time and all subsequent are fast.
That's what caches are for.
HTH,
Tino.
--
www.craniosacralzentrum.de
www.spiritualdesign-chemnitz.de
Tino Schwarze * Lortzingstraße 21 * 09119 Chemnitz
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
_id from table limit 1 offset 10;
will run the query and show you how the query plan looks and how much
time is spent. It is likely to say "SeqScan" somewhere - a sequential
scan of the tabe.
HTH!
Tino.
--
www.craniosacralzentrum.de
www.spiritualdesign-chemnitz.de
Tino Schw
ook at the network
traffic with and without SSL. That way you'll see what is going on and
whether it's encrypted.
HTH,
Tino.
--
www.craniosacralzentrum.de
www.spiritualdesign-chemnitz.de
Tino Schwarze * Lortzingstraße 21 * 09119 Chemnitz
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
here - we're in an emergency
situation anyway and after that, the whole DB will be clean again, all
indices rebuilt nicely, no bloat in the tables.
Tino.
--
www.craniosacralzentrum.de
www.spiritualdesign-chemnitz.de
Tino Schwarze * Lortzingstraße 21 * 09119 Chemnitz
rating system.
Tino.
--
www.craniosacralzentrum.de
www.spiritualdesign-chemnitz.de
Tino Schwarze * Parkstraße 17h * 09120 Chemnitz
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
over 12 days now? How large is the
database? Did you adjust any settings in postgresql.conf (probably based
on the 8.1 ones)? Do you see anything in PostgreSQL's log file? What
kind of machine are you using (there are a lot of different Sun
boxes...)
Bye,
Tino.
--
www.craniosacralzentrum.d
match.
HTH,
Tino.
--
www.craniosacralzentrum.de
www.lebensraum11.de
www.spiritualdesign-chemnitz.de
Tino Schwarze * Parkstraße 17h * 09120 Chemnitz
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
is dangerous, replace "trust" by a method of your choice! But
it's good for testing.
Bye,
Tino.
--
www.craniosacralzentrum.de
www.lebensraum11.de
www.spiritualdesign-chemnitz.de
Tino Schwarze * Parkstraße 17h * 09120 Chemnitz
---(end of broadcast
ectivity
> layer should be doing the work.
I think, he should go for encrypted backup instead if that's what he
wants to accomplish.
> If you're trying to protect against something else, please clarify.
+1
Bye,
Tino.
--
www.craniosacralzentrum.de
www.lebensrau
p://www.minq.se/products/dbvis/) - the free edition
suffices for simple stuff. You could also try installing knoda,
mergeant, rekall and look at these (I don't know them, just looked into
YaST for available database stuff).
I prefer using psql directly...
Bye,
Tino.
--
www.cranios
he latest 8.2.x version, follow the instructions
here: http://www.postgresql.org/docs/8.2/interactive/installation.html
(You need to install the packages readline-devel and zlib-devel as well
as the gcc compiler).
HTH,
Tino.
--
www.craniosacralzentrum.de
www.spiritualdesign-chemnitz.de
Tino
QL on?
Tino.
--
www.spiritualdesign-chemnitz.de
www.lebensraum11.de
Tino Schwarze * Parkstraße 17h * 09120 Chemnitz
---(end of broadcast)---
TIP 6: explain analyze is your friend
e to watch for transactions waiting for the lock in step 3 - I
do not care for the time it took to perform the work.
Maybe I'll have to code up some perl which remembers the transaction age
if the lock is acquired, then calculates lock holding time? Hm, or I'll
just watch for not yet g
yz" in exclusive mode", but this is
AFTER the lock got acquired. We'd like to notice if lock acquisition
takes very long so we can look around and figure out what's wrong.
How can we achieve this?
Thanks,
Tino.
PS: Version is 8.2.4.
--
www.spiritualdesign-chemnitz.de
www.
rror ("out of memory").
>
> What can a pc user do? Anything?
Tell the user that he/she should use an appropiate WHERE clause. You
might also consider adding more swap to the machine. Then it will go
horribly slow, but it will work.
HTH,
Tino.
--
www.spiritualdesign-chemnitz.d
lect c.relname,l.transaction,l.pid,l.granted,l.mode from pg_class
c,pg_locks l where l.relation = c.oid order by l.pid, l.mode;
HTH,
Tino.
--
www.spiritualdesign-chemnitz.de
www.lebensraum11.de
Tino Schwarze * Parkstraße 17h * 09120 Chemnitz
---(end of broadcast)-
h uses a transaction should always look like
this:
boolean success = false; // default: roll back, e.g. on Exception
connection.openTransaction();
try
{
// perform work
success = true;
}
finally
{
connection.closeTransaction (success);
}
HTH,
Tino.
--
www.spiritualdesign-chemnitz.d
t up AFAIK.
You may try the following shell script to determine the size of all
databases:
#!/bin/bash
#
# Shell script to determine disk usage of PostgreSQL databases.
# by Tino Schwarze/Community4you
PGDATADIR=/data/pgsql/74
PGPORT=5432
PSQLBIN=/opt/pgsql-7.4.3/bin/psql
PGOPTS="-U postgres&q
79 matches
Mail list logo
