[GENERAL] GIN fast update technique and work_mem
Hi, While going through the documentation for GIN fast update technique, I read - "...or if the pending list becomes too large (larger than work_mem), the entries are moved to the main GIN data structure using the same bulk insert techniques used during initial index creation." The "work_mem" links to work_mem setting documentation. Whereas in the src/backend/access/gin/README, as one of the features of GIN, I read - "...* Optimized index creation (Makes use of maintenance_work_mem to accumulate postings in memory.)" So, which one is it - work_mem or maintenance_work_mem? Or are these things unrelated? -- Amit -- 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] GIN fast update technique and work_mem
On 2013-10-24 18:40:46 +0900, Amit Langote wrote: > While going through the documentation for GIN fast update technique, I read - > > "...or if the pending list becomes too large (larger than work_mem), > the entries are moved to the main GIN data structure using the same > bulk insert techniques used during initial index creation." > > The "work_mem" links to work_mem setting documentation. > > Whereas in the src/backend/access/gin/README, as one of the features > of GIN, I read - > > "...* Optimized index creation (Makes use of maintenance_work_mem to > accumulate > postings in memory.)" > > So, which one is it - work_mem or maintenance_work_mem? > > Or are these things unrelated? Yes. One is about the initial index creation, the other about insertions into an existing index. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] GIST index : order Hack : getting the order used by CLUSTER .. USING my_index
Hey List, I would like to be able to get the rows following the order of an index (*NOT* getting an order by accelerated, but only an order defined by an index). Something like this : SELECT my_row FROM my_table ORDER BY the_index ASC where the_index is a GIST index over points. I know there is a possibility as it is exactly what the command CLUSTER my_table USING the_index does. I read the following page : http://www.postgresql.org/docs/9.3/static/indexes-ordering.html, but it is not of great help. How much of a hack is it? Cheers, Rémi-C
Re: [GENERAL] GIN fast update technique and work_mem
On Thu, Oct 24, 2013 at 7:26 PM, Andres Freund wrote: > On 2013-10-24 18:40:46 +0900, Amit Langote wrote: >> While going through the documentation for GIN fast update technique, I read - >> >> "...or if the pending list becomes too large (larger than work_mem), >> the entries are moved to the main GIN data structure using the same >> bulk insert techniques used during initial index creation." >> >> The "work_mem" links to work_mem setting documentation. >> >> Whereas in the src/backend/access/gin/README, as one of the features >> of GIN, I read - >> >> "...* Optimized index creation (Makes use of maintenance_work_mem to >> accumulate >> postings in memory.)" >> >> So, which one is it - work_mem or maintenance_work_mem? >> >> Or are these things unrelated? > > Yes. One is about the initial index creation, the other about insertions > into an existing index. > > Greetings, > Hmm, okay. So, work_mem allocations are also involved in case of GIN index updates (if FASTUPDATE is on)? Thanks. -- Amit -- 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] Replication and fsync
On Thu, Oct 24, 2013 at 10:39 AM, wrote: > Newb question. > > I'm running 9.1 with a slave using streaming replication. A coworker wants > to turn off fsync on the master and insists that the slave will still be in > a usable state if there is a failure on the master. We all know that turning > off fsync is a bad idea, but I was under the impression that the fsync > setting would be replicated to the slave, making it useless as a backup in > this scenario. No. The setting of fsync in the master is not replicated to the standby. > Am I wrong? If I'm wrong, is there still danger to the slave > in this kind of setup? No, I think. Regards, -- Fujii Masao -- 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] GIST index : order Hack : getting the order used by CLUSTER .. USING my_index
=?UTF-8?Q?R=C3=A9mi_Cura?= writes: > I would like to be able to get the rows following the order of an index > (*NOT* getting an order by accelerated, but only an order defined by an > index). Since a GiST index hasn't got any specific internal order, I fail to see the point of this. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Replication and fsync
On 24 October 2013 15:04, Fujii Masao wrote: > On Thu, Oct 24, 2013 at 10:39 AM, wrote: >> Am I wrong? If I'm wrong, is there still danger to the slave >> in this kind of setup? > > No, I think. Corruption due to fsync being off on the master will be replicated to the slave, or - if corruption is bad enough - replication will fail to replicate affected records entirely. Of course, turning fsync off is no guarantee for corruption - it's the other way around: having it on guarantees that you don't get corruption (provided that... etc). You could disable replication while fsync is off. I'd verify the data on the master (by creating a dump, for example) before re-enabling it again, though. -- If you can't see the forest for the trees, Cut the trees and you'll see there is no forest. -- 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] GIST index : order Hack : getting the order used by CLUSTER .. USING my_index
Hello, I'm interested in the tree structure inherent to the gist indexing. I was thinking to retrieve it from order of index. Do you know how I could access it directly? My use case would be to take advantage of this gist ordering to order 2D points s1 : N1 N2 .. Nn so that for any given t > =?UTF-8?Q?R=C3=A9mi_Cura?= writes: > > I would like to be able to get the rows following the order of an index > > (*NOT* getting an order by accelerated, but only an order defined by an > > index). > > Since a GiST index hasn't got any specific internal order, I fail to see > the point of this. > > regards, tom lane >
Re: [GENERAL] Wrong estimate in query plan
Eelke Klein wrote > What I noticed is that are no most common values mentioned ofcourse the > value 1 only occurs once in the column but as all other values are NULL > you > could argue it is a common value. A random sampling is unlikely to choose a record that only appears in 0.1 percent of the table. Two sequential scans plus a hash seems like a good plan. The smaller table is so small a sequential scan is fast. The larger table experts to have all records read so it to should be scanned. Combining with a hash seems sound. The fact the cross-column estimate is way off isn't that big a deal though I'd be curious to hear Tom's opinion on why this is so for educational purposes. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Wrong-estimate-in-query-plan-tp5775727p5775785.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Replication and fsync
Thank you for the answers. I'm still confused. If fsync is not replicated to the slave, then how is replication affected by a corrupt master? If the master dies and there's a commit recorded in the wal log that didn't actually happen, wouldn't the slave still be expected to be in a sane state, with the wal logs accurately reflecting what's on disk? Maybe I just don't understand streaming replication enough. The docs seem to say that synchronous commits mean that the slave also has to verify a write before a transaction is considered complete. How does fsync affect the way/order in which statements are sent to the slave for replication? On Thu, Oct 24, 2013 at 9:42 AM, Alban Hertroys wrote: > On 24 October 2013 15:04, Fujii Masao wrote: > > On Thu, Oct 24, 2013 at 10:39 AM, wrote: > >> Am I wrong? If I'm wrong, is there still danger to the slave > >> in this kind of setup? > > > > No, I think. > > Corruption due to fsync being off on the master will be replicated to > the slave, or - if corruption is bad enough - replication will fail to > replicate affected records entirely. Of course, turning fsync off is > no guarantee for corruption - it's the other way around: having it on > guarantees that you don't get corruption (provided that... etc). > > You could disable replication while fsync is off. I'd verify the data > on the master (by creating a dump, for example) before re-enabling it > again, though. > > -- > If you can't see the forest for the trees, > Cut the trees and you'll see there is no forest. >
Re: [GENERAL] GIST index : order Hack : getting the order used by CLUSTER .. USING my_index
=?UTF-8?Q?R=C3=A9mi_Cura?= writes: > I'm interested in the tree structure inherent to the gist indexing. > I was thinking to retrieve it from order of index. How? A SQL query would have no idea where the index page boundaries were in the sequence of retrieved tuples. > Do you know how I could access it directly? I don't think there's any way to do that without modifying the GiST code. What you really care about here is the contents of the upper index levels, which is something that's not exposed at all outside the index AM. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] GIST index : order Hack : getting the order used by CLUSTER .. USING my_index
Ok, thank you Tom for this precise answer ! I don't understand how the CLUSTER .. USING index command work then. It is supposed to rewrite on disk following index order. Does it do nothing for GIST index? Cheers, Rémi-C 2013/10/24 Tom Lane > =?UTF-8?Q?R=C3=A9mi_Cura?= writes: > > I'm interested in the tree structure inherent to the gist indexing. > > I was thinking to retrieve it from order of index. > > How? A SQL query would have no idea where the index page boundaries were > in the sequence of retrieved tuples. > > > Do you know how I could access it directly? > > I don't think there's any way to do that without modifying the GiST code. > What you really care about here is the contents of the upper index levels, > which is something that's not exposed at all outside the index AM. > > regards, tom lane >
Re: [GENERAL] GIST index : order Hack : getting the order used by CLUSTER .. USING my_index
=?UTF-8?Q?R=C3=A9mi_Cura?= writes: > I don't understand how the CLUSTER .. USING index command work then. > It is supposed to rewrite on disk following index order. Does it do nothing > for GIST index? Nobody has ever demonstrated that CLUSTER has any value for anything except btree indexes. It seems likely to me that it'd actually be counterproductive for indexes like GiST, which depend on data arriving in random order for the highest index levels to end up well-distributed. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL Point In Time Recovery
On Wed, Oct 23, 2013 at 9:10 PM, Jayadevan M wrote: > Hi, > I went through > http://www.postgresql.org/docs/9.3/static/continuous-archiving.html > and set up the archiving process. With this approach, if my database > crashes after a couple of weeks after the base backup is taken, recovering > would mean replaying the WAL logs for about 2 weeks, right? To avoid that, > what is the standard process followed - take a base backup every day or > once a week? > Regards, > Jayadevan > I restore from my base backup plus WAL quite often. It is how I get a fresh dev or test instance when I want one. (It is also how I have confidence that everything is working well and that I know what I'm doing should the time come to do a real restore). When that starts to take an annoyingly long time, I run a new base backup. How often that is, can be anywhere from days to months, depending on what's going on in the database. Cheers, Jeff
Re: [GENERAL] GIST index : order Hack : getting the order used by CLUSTER .. USING my_index
Great, thanks. Now you say that I never saw any improvement when clustering table with gist. You just saved me a lot of unnecessary queries :-) Cheers, Rémi-C 2013/10/24 Tom Lane > =?UTF-8?Q?R=C3=A9mi_Cura?= writes: > > I don't understand how the CLUSTER .. USING index command work then. > > It is supposed to rewrite on disk following index order. Does it do > nothing > > for GIST index? > > Nobody has ever demonstrated that CLUSTER has any value for anything > except btree indexes. It seems likely to me that it'd actually be > counterproductive for indexes like GiST, which depend on data arriving in > random order for the highest index levels to end up well-distributed. > > regards, tom lane >
Re: [GENERAL] PostgreSQL Point In Time Recovery
On 10/24/2013 9:47 AM, Jeff Janes wrote: I restore from my base backup plus WAL quite often. It is how I get a fresh dev or test instance when I want one. (It is also how I have confidence that everything is working well and that I know what I'm doing should the time come to do a real restore). When that starts to take an annoyingly long time, I run a new base backup. How often that is, can be anywhere from days to months, depending on what's going on in the database. hey, silly idea formed on half a cup of coffee if that base backup is in the form of a copy of the data directory (as opposed to tar.gz or something), could you 'update' it by pointing an instance of postgres at it, and then playing the WAL archive into it, then shutting that instance down? or would it be impossible to synchronize the ongoing new WAL's from the master with the timeline of this? -- john r pierce 37N 122W somewhere on the middle of the left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL Point In Time Recovery
On Thursday, October 24, 2013 11:31:38 AM John R Pierce wrote: > On 10/24/2013 9:47 AM, Jeff Janes wrote: > > I restore from my base backup plus WAL quite often. It is how I get a > > fresh dev or test instance when I want one. (It is also how I have > > confidence that everything is working well and that I know what I'm > > doing should the time come to do a real restore). When that starts to > > take an annoyingly long time, I run a new base backup. How often that > > is, can be anywhere from days to months, depending on what's going on > > in the database. > > hey, silly idea formed on half a cup of coffee if that base backup > is in the form of a copy of the data directory (as opposed to tar.gz or > something), could you 'update' it by pointing an instance of postgres at > it, and then playing the WAL archive into it, then shutting that > instance down? or would it be impossible to synchronize the ongoing > new WAL's from the master with the timeline of this? That's basically what warm standby's do, isn't it? As long as they keep recovery open it should work. You can also use rsync to take your base backup - just update the rsync copy. That's what I do (and keep a separate tarball of that rsync copy, made on the backup host). -- 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] Replication and fsync
On Wed, Oct 23, 2013 at 6:39 PM, wrote: > Newb question. > > I'm running 9.1 with a slave using streaming replication. A coworker wants > to turn off fsync on the master and insists that the slave will still be in > a usable state if there is a failure on the master. > This would only be safe if you made sure to shoot the master in the head after a crash. If the master ever comes back up again, through automatic recovery or through start-up scripts, it could start feeding corrupt WAL records to the slave, corrupting it as well. Cheers, Jeff
Re: [GENERAL] Count of records in a row
On 10/22/13, Elliot wrote:
> It looks like you already found a solution, but here's one with a CTE. I
> cobbled this together from an older query I had for doing something
> similar, for which I unfortunately lost the original source of this
> approach. Also, this implies that there is something that gives an
> ordering to these rows (in this case, the field "i").
>
> create temp table data (i int, val char);
>
> insert into data (val, i)
> values
> ('A',1),
> ('A',2),
> ('A',3),
> ('B',4),
> ('C',5),
>
> with x
> as
> (
>select i,
> row_number() over () as xxx,
> val,
> row_number() over (partition by val order by i asc)
> - row_number() over () as d
>from data
>order by i
> )
> select val,
> count(*)
> from x
> group by d,
> val
> order by min(i)
> ;
Elliot - Thanks for this great solution; I've tested in on my data and
it gives great results.
I'd like to understand your code. I believe I understand most of it.
Can you explain what 'd' is?
And this clause "row_number() over (partition by val order by i asc) -
row_number() over () as d"?
(Hey, while I'm at it, is there a descriptive name for "x" too?)
Thanks
--
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] Replication and fsync
On Oct 24, 2013, at 18:10, maillis...@gmail.com wrote: > Thank you for the answers. I'm still confused. If fsync is not replicated to > the slave, then how is replication affected by a corrupt master? If the > master dies and there's a commit recorded in the wal log that didn't actually > happen, wouldn't the slave still be expected to be in a sane state, with the > wal logs accurately reflecting what's on disk? > > Maybe I just don't understand streaming replication enough. The docs seem to > say that synchronous commits mean that the slave also has to verify a write > before a transaction is considered complete. How does fsync affect the > way/order in which statements are sent to the slave for replication? What you're missing is that the master will be replicating corrupt data. That is, _if_ it gets corrupted of course. But, data corruption in a database has a tendency to go unnoticed for a while. A corrupted master doesn't necessarily break down immediately - in fact, it can remain running for quite a while as long as the corruption doesn't break stuff in the wrong places or as long as the corrupted records don't get fetched. Until that time, corruption is just blocks of data on disk, which quite possibly end up being replicated to the slave. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Is there something like a limited superuser to give to a db-assistant?
Hi, how can I give adb-assistant the rights to create and drop schemas, tables, views ... BUT keep him out of certain existing schemas and tables? This position of an restricted db assistant is new for us and it wasn't considered in our access rights until now. Our DB got a wee bit komplex with about 400 schemas and 5000 tables. So it would get rather ugly having to alter the rights for every db-object one by one manually.
[GENERAL] Need help how to manage a couple of daily DB copies.
Hi, I'd like to set up a DB-Server that keeps copies of our productive db for an external db-assistant. He should prepare chores on the test-server and mail the sql scripts to me. I'll look over those scripts and run them against the productive db myself. So I'd like to have a daily cron job dump the main db, rename the test-db to something with a date in it. Like test_db --> test_db_20131024 Create a new test_db and import the dump of the main db. So far no problem but how could I limit the number of test_dbs to 5? I'd like to keep those test_dbs 5 days and then drop them.
Re: [GENERAL] Is there something like a limited superuser to give to a db-assistant?
On 25 Říjen 2013, 3:53, Andreas wrote: > Hi, > > how can I give adb-assistant the rights to create and drop schemas, > tables, views ... BUT keep him out of certain existing schemas and tables? > > This position of an restricted db assistant is new for us and it wasn't > considered in our access rights until now. > > Our DB got a wee bit komplex with about 400 schemas and 5000 tables. > So it would get rather ugly having to alter the rights for every > db-object one by one manually. Hi Andreas, I think you should read this: http://www.postgresql.org/docs/9.3/static/sql-grant.html You don't need superuser privileges to create schemas and so on, and by definition "superuser" means "unrestricted". Just create a regular user and grant him CREATE on the database. You may also grant him access only to selected schemas/tables. regards Tomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Is there something like a limited superuser to give to a db-assistant?
On Fri, Oct 25, 2013 at 10:53 AM, Andreas wrote: > Hi, > > how can I give a db-assistant the rights to create and drop schemas, tables, > views ... BUT keep him out of certain existing schemas and tables? Depending on what you want to do, you will have to use a combination GRANT and REVOKE to authorize and to restrict access to multiple database objects: http://www.postgresql.org/docs/9.3/static/sql-grant.html http://www.postgresql.org/docs/9.3/static/sql-revoke.html > Our DB got a wee bit komplex with about 400 schemas and 5000 tables. > So it would get rather ugly having to alter the rights for every db-object > one by one manually. So... First grant the access to all the objects for this new user, and then use revoke on each object individually you want to restrict for him. It would be better to do that at the schema level perhaps... However the risk here is to forget to restrict the access to some objects... So for safety you should do it the other way around. Regards, -- Michael -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help how to manage a couple of daily DB copies.
On 25 Říjen 2013, 3:53, Andreas wrote: > Hi, > > I'd like to set up a DB-Server that keeps copies of our productive db > for an external db-assistant. > He should prepare chores on the test-server and mail the sql scripts to > me. > I'll look over those scripts and run them against the productive db > myself. > > So I'd like to have a daily cron job dump the main db, rename the > test-db to something with a date in it. > Like test_db --> test_db_20131024 > Create a new test_db and import the dump of the main db. > > So far no problem but how could I limit the number of test_dbs to 5? > I'd like to keep those test_dbs 5 days and then drop them. Hi, I assume that's something that needs to be done by your script, there's certainly nothing in PostgreSQL itself to do that. You may for example run a daily cron script that lists all databases on the test server, parses the database name and drops those older than 5 days. Tomas -- 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] Need help how to manage a couple of daily DB copies.
On Fri, Oct 25, 2013 at 10:53 AM, Andreas wrote: > Hi, > > I'd like to set up a DB-Server that keeps copies of our productive db for an > external db-assistant. > He should prepare chores on the test-server and mail the sql scripts to me. > I'll look over those scripts and run them against the productive db myself. > > So I'd like to have a daily cron job dump the main db, rename the test-db to > something with a date in it. > Like test_db --> test_db_20131024 > Create a new test_db and import the dump of the main db. > > So far no problem but how could I limit the number of test_dbs to 5? > I'd like to keep those test_dbs 5 days and then drop them. A simple script kicked by a cron job would do the work for you easily... -- Michael -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help how to manage a couple of daily DB copies.
On Friday, October 25, 2013 03:53:14 AM Andreas wrote:
> Hi,
>
> I'd like to set up a DB-Server that keeps copies of our productive db
> for an external db-assistant.
> He should prepare chores on the test-server and mail the sql scripts to me.
> I'll look over those scripts and run them against the productive db myself.
>
> So I'd like to have a daily cron job dump the main db, rename the
> test-db to something with a date in it.
> Like test_db --> test_db_20131024
> Create a new test_db and import the dump of the main db.
>
> So far no problem but how could I limit the number of test_dbs to 5?
> I'd like to keep those test_dbs 5 days and then drop them.
#delete files older than 5 days
$ find . -mtime +4 -exec rm '{}' \;
Put that in cron too :)
--
Regards
Shridhar
--
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] Need help how to manage a couple of daily DB copies.
Am 25.10.2013 04:15, schrieb Shridhar Daithankar:
On Friday, October 25, 2013 03:53:14 AM Andreas wrote:
Hi,
I'd like to set up a DB-Server that keeps copies of our productive db
for an external db-assistant.
He should prepare chores on the test-server and mail the sql scripts to me.
I'll look over those scripts and run them against the productive db myself.
So I'd like to have a daily cron job dump the main db, rename the
test-db to something with a date in it.
Like test_db --> test_db_20131024
Create a new test_db and import the dump of the main db.
So far no problem but how could I limit the number of test_dbs to 5?
I'd like to keep those test_dbs 5 days and then drop them.
#delete files older than 5 days
$ find . -mtime +4 -exec rm '{}' \;
Put that in cron too :)
well, not quite
We are not talking about files but databases within the db server.
Lets keep 3 copies total
the idea is to start with the database db_test today (2013/10/24)
2013/10/25: rename db_test to db_test_13025 and import the latest
dump into a new db_test
2013/10/26: rename db_test to db_test_13026 ... import
2013/10/27: rename db_test to db_test_13027 ... import
2013/10/28: rename db_test to db_test_13028 ... import
Now we've got db_test and 4 older copies.
Find the oldest copy and drop it. --> drop db_test_131025
or better every day drop every copy but the 3 newest.
and so on
this needs to be done by an external cron script or probaply by a
function within the postgres database or any other administrative database.
The point is to give the assistant a test-db where he could mess things up.
In the event he works longer than a day on a task his work shouldn't be
droped completely when the test-db gets automatically replaced.
Regards
Andreasd
--
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] Need help how to manage a couple of daily DB copies.
Dear, Append following command to crontab: D=`date -d -5day +"%Y%m%d"`;echo "DROP DATABASE test_db_$D" | psql Maybe you should change the "psql" to your psql path. -- Original -- From: "Andreas";; Date: Fri, Oct 25, 2013 09:53 AM To: "pgsql-general"; Subject: [GENERAL] Need help how to manage a couple of daily DB copies. Hi, I'd like to set up a DB-Server that keeps copies of our productive db for an external db-assistant. He should prepare chores on the test-server and mail the sql scripts to me. I'll look over those scripts and run them against the productive db myself. So I'd like to have a daily cron job dump the main db, rename the test-db to something with a date in it. Like test_db --> test_db_20131024 Create a new test_db and import the dump of the main db. So far no problem but how could I limit the number of test_dbs to 5? I'd like to keep those test_dbs 5 days and then drop them.
Re: [GENERAL] Replication and fsync
Dear According to manual, when you set "synchronous_commit" to on, the transaction commits will wait until master and slave flush the commit record of transaction to the physical storage, so I think even if turn off the fsync on master is safe for data consistency and data will not be lost if slave physical storage is not damaged. If "synchronous_commit" is set to remote_write, the transaction commits will wait until slave to write the data to os. Data will lose if master crashed and slave os crashed. But acording to WAL documents it will not crash the data consistency on slave if slave fsync not off. Otherwise fsync off on master may will result in losing data when master crashes, but still keep consistency on the slave if the slave is not crashed or slave's fsync is on . See follow: http://www.postgresql.org/docs/current/static/runtime-config-wal.html#GUC-FSYNC fsync and synchronous_commit http://www.postgresql.org/docs/current/static/wal-intro.html -- Original -- From: "maillists0";; Date: Thu, Oct 24, 2013 09:39 AM To: "pgsql-general"; Subject: [GENERAL] Replication and fsync Newb question. I'm running 9.1 with a slave using streaming replication. A coworker wants to turn off fsync on the master and insists that the slave will still be in a usable state if there is a failure on the master. We all know that turning off fsync is a bad idea, but I was under the impression that the fsync setting would be replicated to the slave, making it useless as a backup in this scenario. Am I wrong? If I'm wrong, is there still danger to the slave in this kind of setup? Can I count on it remaining unharmed if the master suffers unrecoverable corruption?
Re: [GENERAL] PostgreSQL Point In Time Recovery
Jeff Janes wrote > I restore from my base backup plus WAL quite often. It is how I get a > fresh dev or test instance when I want one. (It is also how I have > confidence that everything is working well and that I know what I'm doing > should the time come to do a real restore). When that starts to take an > annoyingly long time, I run a new base backup. How often that is, can be > anywhere from days to months, depending on what's going on in the > database. > > Cheers, > > Jeff That makes sense. So we take a new base backup once we feel "Hey , recovery may take time". Thanks. -- View this message in context: http://postgresql.1045698.n5.nabble.com/PostgreSQL-Point-In-Time-Recovery-tp5775717p5775872.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL Point In Time Recovery
Alan Hodgson wrote > That's basically what warm standby's do, isn't it? As long as they keep > recovery open it should work. A warn standby will be almost in sync with the primary, right? So recovery to point-in-time (like 10 AM this morning) won't be possible. We need a base, but it shouldn't be so old that it takes hours to catchup- that was my thought. As John mentioned, looking at the WAL/transaction numbers, time to recover etc need to be looked at. -- View this message in context: http://postgresql.1045698.n5.nabble.com/PostgreSQL-Point-In-Time-Recovery-tp5775717p5775874.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
