Re: [GENERAL] Vacuuming strategy
Hi: On Wed, Apr 30, 2014 at 7:40 PM, Elanchezhiyan Elango wrote: > Francisco, > Thanks for the partitioning idea. I used to have the tables partitioned. But > now that I have moved to a schema where data is split across about ~90 > tables I have moved away from partitioning. But it's something I have to > reconsider at least for the high traffic tables. I've discovered paritioning lends itself to some nifty tricks on high traffic tables, specially when data is partitioned by a timestamp key correlated with insertion times ( in our case these are CDRs ). Aside from the easiness of dropping the old partitions and not having to vaccum them, I've been testing something with a couple of tricks. Like dropping indexes on old partitions ( which are no longer queried frequently and whose queries normally always get a full table scan ), or moving no longer updates partitions to a different archived schema ( they still get queried, inheritance takes care of this, but this unclutters my table listings and lets me do a backup optimization. I instruct pg_dump to dump the non-updating schema only whenever I change it and all but the updating one more frequently ( these is for pg_dump backups, for which I keep a rotating series to protect again pilot / software errors and to restore them in auxiliary servers fr testing / data mining, for disk failure we have a separate replication / log archiving setup ). These division also aided me when I had to do a 9.1-9.3 updgrade, as we only needed to dump/restore the updating schemas in the downtime window and then redid the archived one after it. Of course I need a full set of scripts to maintiain this, and if you decide to make one of this you'll need an even bigger one. Also, what puzzles me is your insert/update/delete pattern of access. When I've found this I normally have just insert / delete. It seems like you are doing cumulative hourly stats but updating them in place. If this is your case I've found myself doing it ( daily stats updated in place, to have the current day view growing on 'real' time ) and switched to an schema where I inserted into an aux table, which was queried using sum()/avg(), and added and moved the totals once the day was done to another table. It was easier on vacuums, as the daily table just needed a daily vaccum after the big delete, and not even a full one, as the space was going to be reused ( this was before partitioning, and I used a view to query a union of the totals table plus an agregated query of the incremental one, it worked quite well ) Francisco Olarte. -- 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] Return and sql tuple descriptions are incompatible
Joe, that is exactly what I want. Could you please give more detail example for this crosstab ? I have warehouse and product table like this : CREATE TABLE tblwarehouse ( id integer NOT NULL, warehousename character varying(20) ); COPY tblwarehouse (id, warehousename) FROM stdin; 2 OFFICE 3 STORE2 \. CREATE TABLE tblproduct ( id serial NOT NULL, produkid text, warehouseid integer, onhand integer ); COPY tblproduct (produkid, warehouseid, onhand) FROM stdin; 279140414 2 10 279140421 3 10 279140414 3 45 \. I need crosstab query to display record from tblproduct like this : PRODUKID| OFFICE | STORE2 | TOTAL ---+++ -- 279140414 | 10 | 45 | 55 279140421 | 0 | 10 | 10 The crosstab warehouse column name is taken from tblwarehouse so when end user add warehouse, crosstab column name will change automatically. And also each row has total qty. Please give detail command for this. Thanks in advance ! -Original Message- From: Joe Conway [mailto:m...@joeconway.com] Sent: Wednesday, April 30, 2014 5:43 AM To: Hengky Liwandouw Subject: Re: [GENERAL] Return and sql tuple descriptions are incompatible -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 04/25/2014 11:55 PM, Hengky Liwandouw wrote: > The last question : how to have automatically column header from > other table ? say from select distinct warehousename from test > order by 1 ? If I understand your question, the best thing is to run the query "select distinct warehousename from test order by 1" in your application first, and then use the result to build a crosstab SQL string. Then execute the crosstab SQL string with a second query. Joe - -- Joe Conway credativ LLC: http://www.credativ.us Linux, PostgreSQL, and general Open Source Training, Service, Consulting, & 24x7 Support -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.14 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/ iQIcBAEBAgAGBQJTYBz6AAoJEDfy90M199hlybAP/0wfDVvJmvGcXK9lP0w+1vxR A+Snl/E5MjUd3p9yTjBqP7MjDOgg467cn5gg+q7dtpya/jyED4Db78rn/G03ZqVK 2IVhaXQgD6p91w/s+zexdB7UBC3BxGzk/IMf3E93tlsZuBUk15x98jhY4FHl9Wgw ++luWY05pxnuluvmjwvc3e2PM99Re8EIw83KuiLzSYgChCvremz1uJi6hd0GDXme iSmxhgn9blSL5hqJNsYWn0Ch0ga87T380HLOgFgnA4e9afE/8QU8kqHtLt+J9mKF RgzvG2+cPCtlDmjTEWWbznKa+m54VmSnwwLjndU1JOsr4NEh4X5lv8Ahx6yh/BI0 PsoyU/DLrCJcXp263nUuGKbid+PRbRecpX5abX+fP/dfHPNqiw5ECFVpFMiZ35ug 5BqxJPX7hJAapwGp7QBKT9aFCtpuKFRkanywO19lgQC8MVXpRZH+/fADbzYrLc+d v/9u6r4Qhxn7ltEjz7pU85EgZqYLw4j4fXRr1fZseN3+HXZpRVaBGC8JOyPE6Buc p75tlgL7E6XXLNJsoY6RTqAcs3SmPgBBrmAfaP3etbpeHlZUBQMx9Xs2lOXWatn+ Uw3whFjJF1Wl8P+l5Bc49Yyerxj+d1Yb7Z3KOpLglOHi4K8hNu4knaeR1DiOs/4n GLf3JS+5qijpX0aOndVK =5sxK -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Backups over slave instead master?
I've a basic setup with async replication between two distant, geographically separated servers over vpn. Replication happens every 0.5 seconds or so, and is incredible reliable. Today, I've been using backup on master server every twelve hours. I'm wondering if would be possible to execute these backups in the slave server instead, so I can avoid the overhead of backups on master system? Thanks, Edson -- 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] Backups over slave instead master?
On 05/01/2014 10:31 AM, Edson Richter wrote:
I'm wondering if would be possible to execute these backups in the slave
server instead, so I can avoid the overhead of backups on master system?
If you're on PostgreSQL 9.3, you can backup the slave server safely. If
not, you'll need to run this command on the master system first:
SELECT pg_start_backup('some-label');
After the backup is done, run this on the master server:
SELECT pg_stop_backup();
--
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
[GENERAL] break table into portions for writing to separate files
Hello, I've been looking for a way to write a table into multiple files, and am wondering if there are some clever suggestions. Say we have a table that is too large (several Gb) to write to a file that can be used for further analyses in other languages. The table consists of a timestamp field and several numeric fields, with records every 10th of a second. It could be meaningfully broken down into subsets of say 20 minutes worth of records. One option is to write a shell script that loops through the timestamp, selects the corresponding subset of the table, and writes it as a unique file. However, this would be extremely slow because each select takes several hours, and there can be hundreds of subsets. Is there a better way? Cheers, -- Seb -- 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] break table into portions for writing to separate files
Hi,
Does the below kind of approach work for you. I haven't tested this, but
would like to give an idea something like below.
Create a plpgsql function which takes 3 parameters as "From Date", "To
Date" and "Interval".
prev_interval := '0'::interval;
LOOP
IF ( "From Date" + "Interval" <= "To Date") THEN
EXECUTE FORMAT (
$$
COPY (SELECT FROM WHERE timestamp_column >=%s
AND timestamp_column<%s) TO '%s.csv'
$$,
("From Date" + "prev_interval")::TEXT,
("From Date" + "Interval") ::TEXT,
( Filename || (Extract(Epoch from interval)/60)::BIGINT)::TEXT);
prev_interval := "Interval";
"Interval" := "Interval" + "Interval";
ELSE
EXIT FROM LOOP;
END IF;
END LOOP;
Thanks,
Dinesh
manojadinesh.blogspot.com
On Thu, May 1, 2014 at 11:20 PM, Seb wrote:
> Hello,
>
> I've been looking for a way to write a table into multiple files, and am
> wondering if there are some clever suggestions. Say we have a table
> that is too large (several Gb) to write to a file that can be used for
> further analyses in other languages. The table consists of a timestamp
> field and several numeric fields, with records every 10th of a second.
> It could be meaningfully broken down into subsets of say 20 minutes
> worth of records. One option is to write a shell script that loops
> through the timestamp, selects the corresponding subset of the table,
> and writes it as a unique file. However, this would be extremely slow
> because each select takes several hours, and there can be hundreds of
> subsets. Is there a better way?
>
> Cheers,
>
> --
> Seb
>
>
>
> --
> 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] break table into portions for writing to separate files
Hi: On Thu, May 1, 2014 at 7:50 PM, Seb wrote: > I've been looking for a way to write a table into multiple files, and am > wondering if there are some clever suggestions. Say we have a table > that is too large (several Gb) to write to a file that can be used for > further analyses in other languages. The table consists of a timestamp > field and several numeric fields, with records every 10th of a second. > It could be meaningfully broken down into subsets of say 20 minutes > worth of records. One option is to write a shell script that loops > through the timestamp, selects the corresponding subset of the table, > and writes it as a unique file. However, this would be extremely slow > because each select takes several hours, and there can be hundreds of > subsets. Is there a better way? As you mention looping and a shell, I suppose you are in something unix like, with pipes et al. You can pipe COPY ( either with the pipe options for copy, or piping a psql command, or whichever thing you like ) through a script which spits ecah data chunk into its corresponding file. If your data is somehow clustered into the table ( by chance or by design ) you don't even need a to sort the data, just use an open file pool, I did that once with call files, chunked them into day sized files and it worked like a charm ( and if you need the files sorted, you can then use sort on each of them, which normally is quite fast ). For your description of data, with a pipe, you could read a line, extract a key for the record ( the timestamp rounded down to 20 minutes would be a good one ), get and open output file for append ( using a small caching layer ) , write it. Depending on how many files you expect, how many RAM you have and how many files your OS allows you to open, other solutions exists. And if you do not have enough ram / openfiles / clustering for any of them there are multitude of tricks ( if, say, you have 3 years worth, no correlation, and can only open/buffer about 1000 files you could split from the db into day sized chunks and then split each of them into 20 minutes ones. Regards. Francisco Olarte. -- 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] break table into portions for writing to separate files
Hi, several Gb is about 1GB, that's not too much. In case you meant 'several GB', that shouldn't be a problem as well. The first thing I'd do would be creating an index on the column used for dividing the data. Then I'd just use the command COPY with a proper select to save the data to a file. If each select lasts for several hours, make the select faster. Good index usually helps. You can also post here the query which lasts for too long, and attach its plan as well. regards, Szymon On 1 May 2014 19:50, Seb wrote: > Hello, > > I've been looking for a way to write a table into multiple files, and am > wondering if there are some clever suggestions. Say we have a table > that is too large (several Gb) to write to a file that can be used for > further analyses in other languages. The table consists of a timestamp > field and several numeric fields, with records every 10th of a second. > It could be meaningfully broken down into subsets of say 20 minutes > worth of records. One option is to write a shell script that loops > through the timestamp, selects the corresponding subset of the table, > and writes it as a unique file. However, this would be extremely slow > because each select takes several hours, and there can be hundreds of > subsets. Is there a better way? > > Cheers, > > -- > Seb > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
[GENERAL] Ubuntu Packages / Config Files
All, apologies if this has been addressed somewhere already. I don't have a lot of experience in PostgreSQL; this is my first setup where I'm trying to scale and provide some of the more advanced features (like WAL shipping, master-slave sync, integrating pgbouncer, etc.), and I'm looking for help regarding the configuration files. I'm using Ubuntu 12.04 for these deployments at the moment. The Ubuntu packages don't put the configuration files with the cluster data (by default under /var/lib/postgresql/9.1/main under 12.04), but in /etc/postgresql/9.1/main) and they start postgres with the -c option pointing there. Whenever I try to add a slave, first I stop the postgresql service, move the above data directory to something like /var/lib/postgresql/9.1/main.orig, create a new 'main' directory with identical permissions/ownerships, and start pg_basebackup pointing there. It will not copy the server.crt and server.key symlinks (by default pointing to the "snakeoil" cert/key) so I re-create those. I then put the appropriate recovery.conf into /etc/postgresql/9.1/main, given that that's the configuration directory where everything is. I set "wal_level = hot_standby" and "hot_standby = on" in postgresql.conf. After I then start the postgresql service again. The problem is that recovery.conf gets ignored in this case. I can add another symlink pointing to it into the data directory, for example, or copy the file there, then it works, but honestly this has cost me a LOT of time figuring out. So, a couple of questions: 1) am I even going about this the right way under an Ubuntu system? 2) do the packages available at apt.postgresql.org behave differently? 3) do later versions of postgresql behave differently? Eventually, I'd like to use configuration management tools like puppet to deploy something like that, but I suppose that's a topic for another day. Any pointers appreciated, Stephan -- 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] break table into portions for writing to separate files
On Thu, 1 May 2014 20:20:23 +0200, Francisco Olarte wrote: [...] > As you mention looping and a shell, I suppose you are in something > unix like, with pipes et al. You can pipe COPY ( either with the pipe > options for copy, or piping a psql command, or whichever thing you > like ) through a script which spits ecah data chunk into its > corresponding file. If your data is somehow clustered into the table ( > by chance or by design ) you don't even need a to sort the data, just > use an open file pool, I did that once with call files, chunked them > into day sized files and it worked like a charm ( and if you need the > files sorted, you can then use sort on each of them, which normally is > quite fast ). > For your description of data, with a pipe, you could read a line, > extract a key for the record ( the timestamp rounded down to 20 > minutes would be a good one ), get and open output file for append ( > using a small caching layer ) , write it. > Depending on how many files you expect, how many RAM you have and how > many files your OS allows you to open, other solutions exists. And if > you do not have enough ram / openfiles / clustering for any of them > there are multitude of tricks ( if, say, you have 3 years worth, no > correlation, and can only open/buffer about 1000 files you could split > from the db into day sized chunks and then split each of them into 20 > minutes ones. Thanks, I'm glad to hear you've used this approach successfully. It seems as though the best solution is to do a single SELECT to get the data out of the server (it is a view with a very complex query plan joining several other similar views), and then pipe the output through say awk to break down into chunks for writing the files, as you describe. Cheers, -- Seb -- 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] break table into portions for writing to separate files
On Thu, 1 May 2014 20:22:26 +0200, Szymon Guz wrote: > Hi, several Gb is about 1GB, that's not too much. In case you meant > 'several GB', that shouldn't be a problem as well. Sorry, I meant several GB. Although that may not be a problem for PostgreSQL, it is for post-processing the output file with other tools. > The first thing I'd do would be creating an index on the column used > for dividing the data. Then I'd just use the command COPY with a > proper select to save the data to a file. I should have mentioned that this is quite a complex view (not a table), which joins several other views of similar complexity. I'm not sure whether indexes are useful/feasible in this case. I'll investigate. Thanks, -- Seb -- 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] break table into portions for writing to separate files
On 01/05/14 19:50, Seb wrote: > Hello, > > I've been looking for a way to write a table into multiple files, and am > wondering if there are some clever suggestions. Say we have a table > that is too large (several Gb) to write to a file that can be used for > further analyses in other languages. The table consists of a timestamp > field and several numeric fields, with records every 10th of a second. > It could be meaningfully broken down into subsets of say 20 minutes > worth of records. One option is to write a shell script that loops > through the timestamp, selects the corresponding subset of the table, > and writes it as a unique file. However, this would be extremely slow > because each select takes several hours, and there can be hundreds of > subsets. Is there a better way? # copy (select * from generate_series(1,1000)) to program 'split -l 100 - /tmp/xxx'; COPY 1000 # \q $ ls -l /tmp/xxxa* -rw--- 1 postgres postgres 292 May 1 19:08 /tmp/xxxaa -rw--- 1 postgres postgres 400 May 1 19:08 /tmp/xxxab -rw--- 1 postgres postgres 400 May 1 19:08 /tmp/xxxac -rw--- 1 postgres postgres 400 May 1 19:08 /tmp/xxxad -rw--- 1 postgres postgres 400 May 1 19:08 /tmp/xxxae -rw--- 1 postgres postgres 400 May 1 19:08 /tmp/xxxaf -rw--- 1 postgres postgres 400 May 1 19:08 /tmp/xxxag -rw--- 1 postgres postgres 400 May 1 19:08 /tmp/xxxah -rw--- 1 postgres postgres 400 May 1 19:08 /tmp/xxxai -rw--- 1 postgres postgres 401 May 1 19:08 /tmp/xxxaj Each of those contains 100 lines. Torsten -- 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] break table into portions for writing to separate files
On Thu, 01 May 2014 21:12:46 +0200, Torsten Förtsch wrote: [...] > # copy (select * from generate_series(1,1000)) to program 'split -l > 100 - /tmp/xxx'; COPY 1000 # \q > $ ls -l /tmp/xxxa* -rw--- 1 postgres postgres 292 May 1 19:08 > /tmp/xxxaa -rw--- 1 postgres postgres 400 May 1 19:08 /tmp/xxxab > -rw--- 1 postgres postgres 400 May 1 19:08 /tmp/xxxac -rw--- 1 > postgres postgres 400 May 1 19:08 /tmp/xxxad -rw--- 1 postgres > postgres 400 May 1 19:08 /tmp/xxxae -rw--- 1 postgres postgres 400 > May 1 19:08 /tmp/xxxaf -rw--- 1 postgres postgres 400 May 1 19:08 > /tmp/xxxag -rw--- 1 postgres postgres 400 May 1 19:08 /tmp/xxxah > -rw--- 1 postgres postgres 400 May 1 19:08 /tmp/xxxai -rw--- 1 > postgres postgres 401 May 1 19:08 /tmp/xxxaj > Each of those contains 100 lines. Wonderful! I didn't know about this 'PROGRAM' parameter for COPY nowadays. Although the SELECT is slow, the split will happen very quickly this way, so this should be acceptable. Thanks, -- Seb -- 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] Ubuntu Packages / Config Files
On 01/05/2014 19:40, Stephan Fabel wrote: > All, > > apologies if this has been addressed somewhere already. I don't have a > lot of experience in PostgreSQL; this is my first setup where I'm trying > to scale and provide some of the more advanced features (like WAL > shipping, master-slave sync, integrating pgbouncer, etc.), and I'm > looking for help regarding the configuration files. > > I'm using Ubuntu 12.04 for these deployments at the moment. The Ubuntu > packages don't put the configuration files with the cluster data (by > default under /var/lib/postgresql/9.1/main under 12.04), but in > /etc/postgresql/9.1/main) and they start postgres with the -c option > pointing there. > > Whenever I try to add a slave, first I stop the postgresql service, move > the above data directory to something like > /var/lib/postgresql/9.1/main.orig, create a new 'main' directory with > identical permissions/ownerships, and start pg_basebackup pointing > there. It will not copy the server.crt and server.key symlinks (by > default pointing to the "snakeoil" cert/key) so I re-create those. I > then put the appropriate recovery.conf into /etc/postgresql/9.1/main, > given that that's the configuration directory where everything is. I set > "wal_level = hot_standby" and "hot_standby = on" in postgresql.conf. > After I then start the postgresql service again. You haven't made it clear that you are actually replicating to a different PostgreSQL server (whether on the same machine or on another one) - is that the case? Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] Backups over slave instead master?
On Thu, May 1, 2014 at 8:54 AM, Shaun Thomas wrote:
> On 05/01/2014 10:31 AM, Edson Richter wrote:
>
> I'm wondering if would be possible to execute these backups in the slave
>> server instead, so I can avoid the overhead of backups on master system?
>>
>
> If you're on PostgreSQL 9.3, you can backup the slave server safely. If
> not, you'll need to run this command on the master system first:
>
> SELECT pg_start_backup('some-label');
>
> After the backup is done, run this on the master server:
>
> SELECT pg_stop_backup();
>
Or alternatively, if "backup" = pg_dump, then backups can taken from the
slave too. Have a look at pg_xlog_replay_pause() + pg_dump +
pg_xlog_replay_resume().
http://www.postgresql.org/docs/current/static/functions-admin.html#FUNCTIONS-RECOVERY-CONTROL-TABLE
Re: [GENERAL] Ubuntu Packages / Config Files
On 05/01/2014 09:35 AM, Raymond O'Donnell wrote: > You haven't made it clear that you are actually replicating to a > different PostgreSQL server (whether on the same machine or on another > one) - is that the case? Ray. Indeed that is the case. Two servers, one master, one slave. Both identical in version: 9.1.12-0ubuntu0.12.04. Thanks, Stephan -- 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] break table into portions for writing to separate files
On 1 May 2014 21:01, Seb wrote: > On Thu, 1 May 2014 20:22:26 +0200, > Szymon Guz wrote: > > > Hi, several Gb is about 1GB, that's not too much. In case you meant > > 'several GB', that shouldn't be a problem as well. > > Sorry, I meant several GB. Although that may not be a problem for > PostgreSQL, it is for post-processing the output file with other tools. > > > > The first thing I'd do would be creating an index on the column used > > for dividing the data. Then I'd just use the command COPY with a > > proper select to save the data to a file. > > I should have mentioned that this is quite a complex view (not a table), > which joins several other views of similar complexity. I'm not sure > whether indexes are useful/feasible in this case. I'll investigate. > Yes, indexes can be used to speed up the view as well. Such a view is nothing more than just a query. regards, Szymon
Re: [GENERAL] break table into portions for writing to separate files
On Thu, 1 May 2014 22:17:24 +0200, Szymon Guz wrote: > On 1 May 2014 21:01, Seb wrote: > On Thu, 1 May 2014 20:22:26 +0200, > Szymon Guz wrote: >> Hi, several Gb is about 1GB, that's not too much. In case you meant >> 'several GB', that shouldn't be a problem as well. > Sorry, I meant several GB. Although that may not be a problem for > PostgreSQL, it is for post-processing the output file with other > tools. >> The first thing I'd do would be creating an index on the column used >> for dividing the data. Then I'd just use the command COPY with a >> proper select to save the data to a file. > I should have mentioned that this is quite a complex view (not a > table), which joins several other views of similar complexity. I'm > not sure whether indexes are useful/feasible in this case. I'll > investigate. > Yes, indexes can be used to speed up the view as well. Such a view is > nothing more than just a query. Help for CREATE INDEX says that its target should be a table or materialized view, so I'm guessing you mean indexes on the relevant fields of the underlying tables. In that case, I already have indexes on those, especially the timestamp fields which are the ones that are used for the heavy query work. Thanks, -- Seb -- 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] break table into portions for writing to separate files
On 1 May 2014 22:24, Seb wrote: > On Thu, 1 May 2014 22:17:24 +0200, > Szymon Guz wrote: > > > On 1 May 2014 21:01, Seb wrote: > > On Thu, 1 May 2014 20:22:26 +0200, > > Szymon Guz wrote: > > >> Hi, several Gb is about 1GB, that's not too much. In case you meant > >> 'several GB', that shouldn't be a problem as well. > > > Sorry, I meant several GB. Although that may not be a problem for > > PostgreSQL, it is for post-processing the output file with other > > tools. > > >> The first thing I'd do would be creating an index on the column used > >> for dividing the data. Then I'd just use the command COPY with a > >> proper select to save the data to a file. > > > I should have mentioned that this is quite a complex view (not a > > table), which joins several other views of similar complexity. I'm > > not sure whether indexes are useful/feasible in this case. I'll > > investigate. > > > Yes, indexes can be used to speed up the view as well. Such a view is > > nothing more than just a query. > > Help for CREATE INDEX says that its target should be a table or > materialized view, so I'm guessing you mean indexes on the relevant > fields of the underlying tables. In that case, I already have indexes > on those, especially the timestamp fields which are the ones that are > used for the heavy query work. > > Can you show us the query plan for the queries you are using, the view definition, and how you query that view? Szymon
Re: [GENERAL] break table into portions for writing to separate files
On Thu, 1 May 2014 22:31:46 +0200, Szymon Guz wrote: [...] > Can you show us the query plan for the queries you are using, the view > definition, and how you query that view? Thanks for your help with this. Here's the view definition (eliding similar column references): ------ CREATE OR REPLACE VIEW public.daily_flux_2013 AS WITH pre AS ( SELECT mot.time_study, ..., wind.wind_speed_u, ..., op.op_analyzer_status, ..., count(wind.wind_speed_u) OVER w AS nwind FROM daily_motion_2013 mot JOIN daily_wind3d_analog_2013 wind USING (time_study) JOIN daily_opath_2013 op USING (time_study) JOIN ( SELECT generate_series('2013-07-28 00:00:00'::timestamp without time zone, '2013-09-13 00:00:00'::timestamp without time zone, '00:20:00'::interval) AS time_20min) ts_20min ON mot.time_study >= ts_20min.time_20min AND mot.time_study < (ts_20min.time_20min + '00:20:00'::interval) WINDOW w AS (PARTITION BY ts_20min.time_20min ORDER BY ts_20min.time_20min) ) SELECT pre.time_study, ..., FROM pre WHERE pre.nwind = 12000 ORDER BY pre.time_study; --- --- Here, mot, wind, and op are views that are similarly constructed (i.e. they use generate_series () and join a few tables). The WHERE clause is used to output only 20 minute periods where every 0.1 second record is available (as determined by the wind_speed_u column). I'm SELECT'ing this view simply as 'SELECT * FROM daily_flux_2013', and that shows this query plan (lengthy output from pgadmin's): --- --- "Sort (cost=29182411.29..29182411.57 rows=111 width=976)" " Sort Key: pre.time_study" " CTE pre" "-> WindowAgg (cost=29181518.64..29181907.52 rows=2 width=434)" " -> Sort (cost=29181518.64..29181574.19 rows=2 width=434)" "Sort Key: (generate_series('2013-07-28 00:00:00'::timestamp without time zone, '2013-09-13 00:00:00'::timestamp without time zone, '00:20:00'::interval))" "-> Nested Loop (cost=22171519.20..29179914.24 rows=2 width=434)" " Join Filter: (((generate_series('2013-07-28 00:00:00'::timestamp without time zone, '2013-09-13 00:00:00'::timestamp without time zone, '00:00:00.1'::interval)) >= (generate_series('2013-07-28 00:00:00'::timestamp without time zone, ' (...)" " -> Result (cost=0.00..5.01 rows=1000 width=0)" " -> Materialize (cost=22171519.20..29175899.74 rows=200 width=426)" "-> Merge Join (cost=22171519.20..29175898.74 rows=200 width=426)" " Merge Cond: ((generate_series('2013-07-28 00:00:00'::timestamp without time zone, '2013-09-13 00:00:00'::timestamp without time zone, '00:00:00.1'::interval)) = (generate_series('2013-07-28 00:00:00'::timestamp without tim (...)" " -> Merge Join (cost=9360527.55..12865370.87 rows=200 width=123)" "Merge Cond: ((generate_series('2013-07-28 00:00:00'::timestamp without time zone, '2013-09-13 00:00:00'::timestamp without time zone, '00:00:00.1'::interval)) = (generate_series('2013-07-28 00:00:00'::timestamp witho (...)" "-> Unique (cost=8625.16..8626.84 rows=200 width=50)" " -> Sort (cost=8625.16..8626.00 rows=337 width=50)" "Sort Key: (generate_series('2013-07-28 00:00:00'::timestamp without time zone, '2013-09-13 00:00:00'::timestamp without time zone, '00:00:00.1'::interval))" "-> Nested Loop (cost=0.57..8611.01 rows=337 width=50)" " -> Result (cost=0.00..5.01 rows=1000 width=0)" " -> Index Scan using motion_series_time_idx on motion_series mot (cost=0.57..8.59 rows=1 width=50)" "Index Cond: ("time" = (generate_series('2013-07-28 00:00:00'::timestamp without time zone, '2013-09-13 00:00:00'::timestamp without time zone, '00:00:00.1'::interval)))" "Filter: (logging_group_id = 33)" "-> Materialize (cost=9351902.39..12856739.03 rows=200 width=73)" " -> Unique (cost=9351902.39..12856736.53 rows=200 width=73)" "-> Merge Join (cost=9351902.39..12369954.70 rows=194712730 width=73)" " Merge Cond: ((generate_ser
Re: [GENERAL] Ubuntu Packages / Config Files
On 05/01/2014 11:40 AM, Stephan Fabel wrote:
I'm using Ubuntu 12.04 for these deployments at the moment. The Ubuntu
packages don't put the configuration files with the cluster data (by
default under /var/lib/postgresql/9.1/main under 12.04), but in
/etc/postgresql/9.1/main) and they start postgres with the -c option
pointing there.
Whenever I try to add a slave, first I stop the postgresql service, move
the above data directory to something like
/var/lib/postgresql/9.1/main.orig, create a new 'main' directory with
identical permissions/ownerships, and start pg_basebackup pointing
there. It will not copy the server.crt and server.key symlinks (by
default pointing to the "snakeoil" cert/key) so I re-create those. I
then put the appropriate recovery.conf into /etc/postgresql/9.1/main,
given that that's the configuration directory where everything is. I set
"wal_level = hot_standby" and "hot_standby = on" in postgresql.conf.
After I then start the postgresql service again.
I built a script that works in our system to create a standby. It's nice
to have something ready-to-go and well tested when you need to quickly
get a standby up and running. I leave the script in the PostgreSQL
config directory where it's tracked, along with other config changes, in
git.
I also keep recovery.conf and the certificate files in
/etc/postgresql/9.1/main where they, too, are revision controlled and
ready to copy into place by the script.
There was some discussion some time ago about changing the requirement
to have recovery.conf and certs in the data directory but I lost track
of what was decided for post-9.1 versions.
My recovery.conf mostly consists of:
standby_mode = on
primary_conninfo = 'host=10.9.8.7 user=standby'
The script, stripped of various error-checking, confirmation screens and
other fluff basically looks like:
### Start
postgresql_replication_user='standby'
postgresql_owner='postgres'
master_ip='10.9.8.7'
pg_parent='/var/lib/postgresql/9.1'
data_dir="${pg_parent}/main"
backup_dir="${pg_parent}/$(date +%Y%m%d_%H%M)"
conf_dir='/etc/postgresql/9.1/main/'
server_crt='/etc/ssl/certs/ssl-cert-snakeoil.pem'
server_key='/etc/ssl/private/ssl-cert-snakeoil.key'
# Stop the server
echo "Stopping PostgreSQL"
/etc/init.d/postgresql stop
# Delete and remake the data directory
echo "Moving data directory"
mv "${data_dir}" "${backup_dir}"
mkdir "${data_dir}"
chown "${postgresql_owner}:${postgresql_owner}" "${data_dir}"
chmod 700 "${data_dir}"
# Switch to postgres user and run basebackup
echo "Re-synchronizing database from master"
su - postgres -c "
pg_basebackup \
--pgdata \"${data_dir}\" \
--xlog \
--host \"${master_ip}\" \
--user \"${postgresql_replication_user}\" \
--verbose \
--progress
"
# Relink the server keys
ln -s "${server_crt}" "${data_dir}/server.crt"
ln -s "${server_key}" "${data_dir}/server.key"
# Put recovery.conf into place
echo "Setting up recovery.conf"
cp -p "${conf_dir}/recovery.conf" "${data_dir}"
# Start the server
echo "Starting standby server"
/etc/init.d/postgresql start
### End
Hope this helps.
Cheers,
Steve
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] break table into portions for writing to separate files
On 1 May 2014 22:50, Seb wrote: > On Thu, 1 May 2014 22:31:46 +0200, > Szymon Guz wrote: > > [...] > > > Can you show us the query plan for the queries you are using, the view > > definition, and how you query that view? > > Thanks for your help with this. Here's the view definition (eliding > similar column references): > > ------ > CREATE OR REPLACE VIEW public.daily_flux_2013 AS > WITH pre AS ( > SELECT mot.time_study, ..., > wind.wind_speed_u, ..., > op.op_analyzer_status, ..., > count(wind.wind_speed_u) OVER w AS nwind >FROM daily_motion_2013 mot > JOIN daily_wind3d_analog_2013 wind USING (time_study) >JOIN daily_opath_2013 op USING (time_study) >JOIN ( SELECT generate_series('2013-07-28 00:00:00'::timestamp without > time zone, '2013-09-13 00:00:00'::timestamp without time zone, > '00:20:00'::interval) AS time_20min) ts_20min ON mot.time_study >= > ts_20min.time_20min AND mot.time_study < (ts_20min.time_20min + > '00:20:00'::interval) > WINDOW w AS (PARTITION BY ts_20min.time_20min ORDER BY > ts_20min.time_20min) > ) > SELECT pre.time_study, ..., >FROM pre > WHERE pre.nwind = 12000 > ORDER BY pre.time_study; > --- --- > > Here, mot, wind, and op are views that are similarly constructed > (i.e. they use generate_series () and join a few tables). The WHERE > clause is used to output only 20 minute periods where every 0.1 second > record is available (as determined by the wind_speed_u column). > > I'm SELECT'ing this view simply as 'SELECT * FROM daily_flux_2013', and > that shows this query plan (lengthy output from pgadmin's): > > --- --- > "Sort (cost=29182411.29..29182411.57 rows=111 width=976)" > " Sort Key: pre.time_study" > " CTE pre" > "-> WindowAgg (cost=29181518.64..29181907.52 rows=2 width=434)" > " -> Sort (cost=29181518.64..29181574.19 rows=2 width=434)" > "Sort Key: (generate_series('2013-07-28 > 00:00:00'::timestamp without time zone, '2013-09-13 00:00:00'::timestamp > without time zone, '00:20:00'::interval))" > "-> Nested Loop (cost=22171519.20..29179914.24 > rows=2 width=434)" > " Join Filter: (((generate_series('2013-07-28 > 00:00:00'::timestamp without time zone, '2013-09-13 00:00:00'::timestamp > without time zone, '00:00:00.1'::interval)) >= (generate_series('2013-07-28 > 00:00:00'::timestamp without time zone, ' (...)" > " -> Result (cost=0.00..5.01 rows=1000 width=0)" > " -> Materialize (cost=22171519.20..29175899.74 > rows=200 width=426)" > "-> Merge Join > (cost=22171519.20..29175898.74 rows=200 width=426)" > " Merge Cond: > ((generate_series('2013-07-28 00:00:00'::timestamp without time zone, > '2013-09-13 00:00:00'::timestamp without time zone, > '00:00:00.1'::interval)) = (generate_series('2013-07-28 > 00:00:00'::timestamp without tim (...)" > " -> Merge Join > (cost=9360527.55..12865370.87 rows=200 width=123)" > "Merge Cond: > ((generate_series('2013-07-28 00:00:00'::timestamp without time zone, > '2013-09-13 00:00:00'::timestamp without time zone, > '00:00:00.1'::interval)) = (generate_series('2013-07-28 > 00:00:00'::timestamp witho (...)" > "-> Unique > (cost=8625.16..8626.84 rows=200 width=50)" > " -> Sort > (cost=8625.16..8626.00 rows=337 width=50)" > "Sort Key: > (generate_series('2013-07-28 00:00:00'::timestamp without time zone, > '2013-09-13 00:00:00'::timestamp without time zone, > '00:00:00.1'::interval))" > "-> Nested Loop > (cost=0.57..8611.01 rows=337 width=50)" > " -> Result > (cost=0.00..5.01 rows=1000 width=0)" > " -> Index Scan > using motion_series_time_idx on motion_series mot (cost=0.57..8.59 rows=1 > width=50)" > "Index > Cond: ("time" = (generate_series('2013-07-28 00:00:00'::timestamp without > time zone, '2013-09-13 00:00:00'::timestamp without time zone, > '00:00:00.1'::interval)))" > "Filter: > (logging_group_id = 33)" > "-> Materialize > (cost=9351902.39..12856739.03 rows=200 width=73)" > " -> Unique > (cost=9351902.39..12856736.53 rows=200 width=73)" > "
Re: [GENERAL] break table into portions for writing to separate files
On Thu, 1 May 2014 23:41:04 +0200, Szymon Guz wrote: [...] > In this form it is quite unreadible. Could you paste the plan to the > http://explain.depesz.com/ and provide her an url of the page? Nice. http://explain.depesz.com/s/iMJi -- Seb -- 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] Vacuuming strategy
On Wed, Apr 30, 2014 at 9:59 AM, Elanchezhiyan Elango wrote: > Hi, > > I need help on deciding my vacuuming strategy. I need to know if I ever > need to do 'vacuum full' for my tables. > > Important and critical configuration is "fillfactor". "fillfactor" will have a greater impact on VACUUMING strategy. It will further help reduce or even remove the dependency on VACUUM FULL. If possible, please set the fillfactor to an optimal value for Tables and Indexes which are undergoing high DMLs. May be you should start with a value between 50 to 70. Only problem is - You will need to do a one-time VACUUM FULL immediately after you change the fillfactor. Regards, Venkata Balaji N Fujitsu Australia
[GENERAL] Revoke Connect
[GENERAL] Revoke - database does not exist
Hi, Do not seem to figure out what is wrong here. Why am I getting database does not exist. I just created the database and am able to connect to it as "postgres" user. I am trying to restrict "testuser" from connecting to "myDB" database. Thanks in advance. postgres@ulinux3:~$ createuser -D -S -R -P testuser Enter password for new role: Enter it again: postgres@ulinux3:~$ createdb myDB postgres@ulinux3:~$ psql psql (9.3.4) Type "help" for help. postgres=# REVOKE ALL PRIVILEGES ON DATABASE myDB FROM testuser; ERROR: database "mydb" does not exist postgres=# \q postgres@ulinux3:~$ psql myDB psql (9.3.4) Type "help" for help. myDB=# REVOKE ALL PRIVILEGES ON DATABASE myDB FROM testuser; ERROR: database "mydb" does not exist --Prashanth Kumar
Re: [GENERAL] Revoke - database does not exist
I guess you need to quote the identifier, as you use mixed case. I.e. try "myDB" with the double quotes. Tomas Dne 2. 5. 2014 2:49 Prashanth Kumar napsal(a): Hi,Do not seem to figure out what is wrong here. Why am I getting database does not exist. I just created the database and am able to connect to it as "postgres" user. I am trying to restrict "testuser" from connecting to "myDB" database. Thanks in advance.postgres@ulinux3:~$ createuser -D -S -R -P testuserEnter password for new role:Enter it again:postgres@ulinux3:~$ createdb myDBpostgres@ulinux3:~$ psqlpsql (9.3.4)Type "help" for help.postgres=# REVOKE ALL PRIVILEGES ON DATABASE myDB FROM testuser;ERROR: database "mydb" does not existpostgres=# \qpostgres@ulinux3:~$ psql myDBpsql (9.3.4)Type "help" for help.myDB=# REVOKE ALL PRIVILEGES ON DATABASE myDB FROM testuser;ERROR: database "mydb" does not exist--Prashanth Kumar
Re: [GENERAL] Revoke - database does not exist
Prashanth Kumar wrote > Hi, > > Do not seem to figure out what is wrong here. Why am I getting database > does not exist. I just created the database and am able to connect to it > as "postgres" user. > I am trying to restrict "testuser" from connecting to "myDB" database. Thomas is likely correct about the quoting. The command programs are case-sensitive and perform the proper quoting for you when issuing commands; but when you are inside psql you need to take care to perform the proper quoting yourself. WRT grant/revoke keep in mind when you revoke you only remove pre-existing grants; it does not establish an explicit block on its own. i.e., it doesn't stop default permissions, or those inherited from PUBLIC, from applying. You would need to remove those inheritance-granted permissions and then explicitly grant them back to those who still require them while omitting those that do not. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Revoke-database-does-not-exist-tp5802183p5802185.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
