Re: [GENERAL] Fastest Index/Algorithm to find similar sentences
On Thu, Jul 25, 2013 at 3:54 PM, Janek Sendrowski wrote: > The Fulltextsearch is not really suitable because it doesn't have a tolerance. What do you exactly mean by tolerance here? -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA Profile: http://www.linkedin.com/in/grayhemp Phone: USA +1 (415) 867-9984, Russia +7 (901) 903-0499, +7 (988) 888-1979 Skype: gray-hemp Jabber: gray...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Fastest Index/Algorithm to find similar sentences
Thanks for your answers. @Amit Langote: I had a look and found out that pg_bigm doesn't support similar matches @Dann Corbit: The idea with the sequences makes sence. I had a look and I'm not sure, if they support similar sequences Janek -- 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] postgres FDW cost estimation options unrecognized in 9.3-beta1
On Fri, Jul 26, 2013 at 3:28 PM, Tom Lane wrote: > Lonni J Friedman writes: >> nightly=# ALTER SERVER cuda_db10 OPTIONS (SET use_remote_estimate 'true') ; >> ERROR: option "use_remote_estimate" not found > >> Am I doing something wrong, or is this a bug? > > [ experiments... ] You need to say ADD, not SET, to add a new option to > the list. SET might more appropriately be spelled REPLACE, because it > requires that the object already have a defined value for the option, > which will be replaced. > > Our documentation appears not to disclose this fine point, but a look > at the SQL-MED standard says it's operating per spec. The standard also > says that ADD is an error if the option is already defined, which is a > bit more defensible, but still not exactly what I'd call user-friendly. > And the error we issue for that case is pretty misleading too: > > regression=# ALTER SERVER cuda_db10 OPTIONS (use_remote_estimate 'true') ; > ALTER SERVER > regression=# ALTER SERVER cuda_db10 OPTIONS (use_remote_estimate 'false') ; > ERROR: option "use_remote_estimate" provided more than once > > I think we could do with both more documentation, and better error > messages for these cases. In the SET-where-you-should-use-ADD case, > perhaps > > ERROR: option "use_remote_estimate" has not been set > HINT: Use ADD not SET to define an option that wasn't already set. > > In the ADD-where-you-should-use-SET case, perhaps > > ERROR: option "use_remote_estimate" is already set > HINT: Use SET not ADD to change an option's value. > > The "provided more than once" wording would be appropriate if the same > option is specified more than once in the command text, but I'm not sure > that it's worth the trouble to detect that case. > > Thoughts, better wordings? Thanks Tom, I've confirmed that using ADD was the solution. I think your suggested updated ERROR & HINT text is an excellent improvement. It definitely would have given me the clue I was missing earlier. -- 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] postgres FDW cost estimation options unrecognized in 9.3-beta1
Lonni J Friedman writes: > nightly=# ALTER SERVER cuda_db10 OPTIONS (SET use_remote_estimate 'true') ; > ERROR: option "use_remote_estimate" not found > Am I doing something wrong, or is this a bug? [ experiments... ] You need to say ADD, not SET, to add a new option to the list. SET might more appropriately be spelled REPLACE, because it requires that the object already have a defined value for the option, which will be replaced. Our documentation appears not to disclose this fine point, but a look at the SQL-MED standard says it's operating per spec. The standard also says that ADD is an error if the option is already defined, which is a bit more defensible, but still not exactly what I'd call user-friendly. And the error we issue for that case is pretty misleading too: regression=# ALTER SERVER cuda_db10 OPTIONS (use_remote_estimate 'true') ; ALTER SERVER regression=# ALTER SERVER cuda_db10 OPTIONS (use_remote_estimate 'false') ; ERROR: option "use_remote_estimate" provided more than once I think we could do with both more documentation, and better error messages for these cases. In the SET-where-you-should-use-ADD case, perhaps ERROR: option "use_remote_estimate" has not been set HINT: Use ADD not SET to define an option that wasn't already set. In the ADD-where-you-should-use-SET case, perhaps ERROR: option "use_remote_estimate" is already set HINT: Use SET not ADD to change an option's value. The "provided more than once" wording would be appropriate if the same option is specified more than once in the command text, but I'm not sure that it's worth the trouble to detect that case. Thoughts, better wordings? 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] how _not_ to log?
On 07/26/2013 10:42 AM, Tim Spencer wrote: On Jul 25, 2013, at 11:45 PM, Joe Van Dyk wrote: Have chef supply the password in encrypted format. Interesting idea. I was hoping that somebody would be able to solve my logging issue instead of me having to rejigger my nice centralized password system. :-) Is there really no way to turn off the logging of those queries? It's not that well documented yet though, as far as I can tell. See this thread: http://www.postgresql.org/message-id/201110272054.p9rksks18...@momjian.us The fact that it's not well documented seems like it might be changed later on. Is this format set in stone? Yes, if you consider the docs stone: http://www.postgresql.org/docs/9.2/static/protocol-flow.html AuthenticationMD5Password Thanks, and have fun! -tspencer -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] DATE type output does not follow datestyle parameter
From: "Adrian Klaver"
Actually the relevant code seems to be here:
/src/ backend/parser/gram.y
CURRENT_DATE
{
/*
* Translate as
"'now'::text::date".
*
CURRENT_TIMESTAMP
{
/*
* Translate as "now()", since
we have a function that
* does exactly what is needed.
...
Also from :
8.5.1.4. Special Values
The following SQL-compatible functions can also be used to obtain the
current time value for the corresponding data type: CURRENT_DATE,
CURRENT_TIME, CURRENT_TIMESTAMP, LOCALTIME, LOCALTIMESTAMP. The latter
four accept an optional subsecond precision specification. (See Section
9.9.4.) Note that these are SQL functions and are not recognized in data
input strings.
It would seem the functions are special cased.
As below, normal DATE constants also does not follow the setting of
datestyle. Is this a bug?
SET datestyle = 'Postgres, MDY';
SELECT DATE 'Jan 2 2013';
date
01-02-2013
(1 row)
Regards
MauMau
--
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] How to do incremental / differential backup every hour in Postgres 9.1?
On Thu, Jul 25, 2013 at 3:24 PM, Neil McGuigan wrote:
> Trying to do an hourly hot incremental backup of a single postgres server
> (windows).
Can you explain what "incremental backup" means to you? I find that
there is a surprising variety of opinions about what these terms mean.
To me, the accumulated wal archive *is* the incremental backup.
> archive_mode=on
> archive_command='copy "%p" "c:\\postgres\\archive\\%f"'
...
>
> Questions:
>
> 1. what command(s) do I run to do a new incremental backup (pg_basebackup
> does a new base backup which I don't want right now)? do I just run select
> pg_start_backup('label'); select pg_stop_backup(); on a schedule?
You shouldn't need to do anything (including pg_start_backup), your
archive_command should already be doing it, inherently. I guess you
need to backup c:\postgres\archive\, if it is not already being
backed up.
> 2. What does the label in pg_basebackup and pg_start_backup() do exactly?
It names your backup. The name is entirely for your convenience.
>
> 3. WAL Files don't seem to be removed from pg_xlog. What should I do about
> that? It seems to keep 5 or so WALs in pg_xlog. Is that to be expected?
If they weren't removed (or recycled), then there would eventually be
far more than 5, so they probably are getting removed as appropriate.
You must have checkpoint_segments set pretty low in for there to be
only 5.
>
> 4. Do I need to backup the .backup files in the archive folder or just the
> 16,384KB WAL files?
The .backup files are not strictly necessary, they are for your
convenience. But they are small and sometimes useful, so why not keep
them? There should be one of them for each base backup you have made.
> 5. should I use the --xlog parameter and if so do I need to change
> wal_keep_segments from 0?
Since you have archive_mode=on, you should not need -xlog unless you
are trying to do something special. I think it just leads to
confusion to use it when it is not necessary.
Cheers,
Jeff
--
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] how _not_ to log?
On Thu, Jul 25, 2013 at 3:59 PM, Tim Spencer wrote: > Hello there! > > I've seen lots of people who have asked questions about how to log > this or that, but I have the opposite question! :-) I'm seeing this in my > logs: > > Jul 25 18:08:11 staging-db11 postgres[27050]: [10-2] STATEMENT: create role > pguser encrypted password 'XXX'; That does not look like the entire message. What was before and after it in the log? For example: ERROR: role "foobar" already exists STATEMENT: create role foobar encrypted password 'XXX'; If it were not for the ERROR, the STATEMENT would not be being logged, in my hands. > > Where XXX is the actual password. This happens every 30 minutes when > my chef client kicks off and resets the passwords. Here's everything that I > have in postgres.conf related to logging: > > log_destination = 'syslog' # Valid values are combinations of > # stderr, csvlog, syslog, and > eventlog, > # depending on platform. csvlog > # requires logging_collector to be on. > logging_collector = on # Enable capturing of stderr and > csvlog > # into log files. Required to be on > for > # csvlogs. > log_directory = 'pg_log'# directory where log files are > written, > log_filename = 'postgresql-%a.log' # log file name pattern, > log_truncate_on_rotation = on # If on, an existing log file with the > # same name as the new log file will > be > log_rotation_age = 1d # Automatic rotation of logfiles will > log_rotation_size = 0 # Automatic rotation of logfiles will > # happen after that much log output. > # DO NOT USE without syslog or > # logging_collector > log_min_duration_statement = 2000 # 2 seconds > log_checkpoints = on What about log_min_error_statement ? > > What I'd like to do is stop logging create role commands, as the logs > end up full of passwords. Is there any way to do this? Thanks, and have fun! First you need to find out why they were getting logged. I don't think any of the setting you showed explain that. Also, I don't think anything you can do will render it acceptable to show your log files to unprivileged users, if that is what you are aiming for. Cheers, Jeff -- 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] how _not_ to log?
On Jul 26, 2013, at 1:42 PM, Tim Spencer wrote: > On Jul 25, 2013, at 11:45 PM, Joe Van Dyk wrote: >> Have chef supply the password in encrypted format. >> > I was hoping that somebody would be able to solve my logging issue instead > of me having to rejigger my nice centralized password system. Another "rejigger" idea would be to use psql meta-command: \password fred It logs a little more friendly: 2013-07-26 13:59:58 EDT,0,idle LOG: 0: statement: ALTER USER fred PASSWORD 'md5af0d89ddc522353ffe41de823a94c0e1' http://www.postgresql.org/docs/9.2/interactive/app-psql.html -- 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] how _not_ to log?
On Jul 25, 2013, at 11:45 PM, Joe Van Dyk wrote: > Have chef supply the password in encrypted format. > Interesting idea. I was hoping that somebody would be able to solve my logging issue instead of me having to rejigger my nice centralized password system. :-) Is there really no way to turn off the logging of those queries? > It's not that well documented yet though, as far as I can tell. See this > thread: > http://www.postgresql.org/message-id/201110272054.p9rksks18...@momjian.us > The fact that it's not well documented seems like it might be changed later on. Is this format set in stone? Thanks, and have fun! -tspencer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] postgres FDW cost estimation options unrecognized in 9.3-beta1
Greetings, I have a postgresql-9.3-beta1 cluster setup (from the yum.postgresql.org RPMs), where I'm experimenting with the postgres FDW extension. The documentation ( http://www.postgresql.org/docs/9.3/static/postgres-fdw.html ) references three Cost Estimation Options which can be set for a foreign table or a foreign server. However when I attempt to set them, I always get an error that the option is not found: ### nightly=# show SERVER_VERSION ; server_version 9.3beta1 nightly=# \des+ List of foreign servers Name| Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW Options| Description ---+---+--+---+--+-+-- -+- cuda_db10 | lfriedman | postgres_fdw | | | | (host 'cuda-db10', dbname 'nightly', port '5432') | (1 row) nightly=# ALTER SERVER cuda_db10 OPTIONS (SET use_remote_estimate 'true') ; ERROR: option "use_remote_estimate" not found ### Am I doing something wrong, or is this a bug? 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 Stopped abruptly
On Fri, Jul 26, 2013 at 9:35 AM, akp geek wrote: > Hi All - > > We have been running postgres 9.0.2 since October. Streaming also in > place. Working flawless. yesterday suddenly the replication stopped. Did you write that correctly? If so, Postgres 9.0 is on patch release 9.0.13. Priority #1 is to install bugfix release and see if you can restart replication. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] REPLICATION Stopped abruptly
Hi All - We have been running postgres 9.0.2 since October. Streaming also in place. Working flawless. yesterday suddenly the replication stopped. When I look at the log file. This is what I have for the last 2 lines and that's it. I don't see any discrepancies. Can you please help ? Thanks for your time 0 2013-07-25 15:08:35 GMT LOG: server process (PID 17419) was terminated by signal 10 0 2013-07-25 15:08:35 GMT LOG: terminating any other active server processes Regards
Re: [GENERAL] DATE type output does not follow datestyle parameter
On 07/26/2013 05:31 AM, MauMau wrote:
> Hello,
>
> The description of datestyle parameter does not seem to match the actual
> behavior. Is this a bug to be fixed? Which do you think should be
> corrected, the program or the manual?
>
>
> The manual says:
>
> DateStyle (string)
> Sets the display format for date and time values, as well as the rules for
> interpreting ambiguous date input values. For historical reasons, this
> variable contains two independent components: the output format
> specification (ISO, Postgres, SQL, or German) and the input/output
> specification for year/month/day ordering (DMY, MDY, or YMD). ...
>
>
> And says:
>
> http://www.postgresql.org/docs/current/static/datatype-datetime.html
>
> 8.5.2. Date/Time Output
> The output of the date and time types is of course only the date or time
> part in accordance with the given examples.
>
>
> After doing "SET datestyle = 'Postgres, MDY'" on the psql prompt, I did the
> following things on the same psql session:
>
>
> 1. SELECT current_timestamp;
>
>now
> --
> Wed Jul 24 10:51:00.217 2013 GMT
> (1 行)
>
> This is exactly as I expected.
>
>
> 2. SELECT current_date;
> I expected the output "Wed Jul 24 2013" or "Jul 24 2013", but I got:
>
> date
>
> 07-24-2013
> (1 行)
>
> This does not follow the above statement in 8.5.2. This output is created
> by EncodeDateOnly() in src/backend/utils/adt/datetime.c.
Actually the relevant code seems to be here:
/src/ backend/parser/gram.y
CURRENT_DATE
{
/*
* Translate as "'now'::text::date".
*
CURRENT_TIMESTAMP
{
/*
* Translate as "now()", since
we have a function that
* does exactly what is needed.
...
Also from :
8.5.1.4. Special Values
The following SQL-compatible functions can also be used to obtain the
current time value for the corresponding data type: CURRENT_DATE,
CURRENT_TIME, CURRENT_TIMESTAMP, LOCALTIME, LOCALTIMESTAMP. The latter
four accept an optional subsecond precision specification. (See Section
9.9.4.) Note that these are SQL functions and are not recognized in data
input strings.
It would seem the functions are special cased.
>
>
> Regards
> MauMau
>
>
>
>
--
Adrian Klaver
adrian.kla...@gmail.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Trigger and deadlock
Loïc Rollus wrote: > I've try to make some concurrency robustness test with an web server app that > use Hibernate and > Postgres. > It seems that my trigger make deadlock when multiple thread use it. > > I will try to simplify examples: > I have a table "films"(id, title,director) and a table > "directors"(id,name,nbreFilms). I want to > automaticaly count directors's films. > > So I have this triggers after each insert on films: > > CREATE OR REPLACE FUNCTION incrementDirectors() RETURNS TRIGGER AS > $incDirectors$ > BEGIN > UPDATE directors > SET nbreFilm = nbreFilm + 1 > WHERE directors.id = NEW.director; > RETURN NEW; > END; > $incDirectors$ LANGUAGE plpgsql; > CREATE TRIGGER triggerIncrDirectors AFTER INSERT ON films FOR EACH ROW > EXECUTE PROCEDURE > incrementDirectors(); > > > When I do a lot of INSERT films at the same time, I have this error: > > ** > > 1286785 22142 2013-07-26 13:55:25 CEST ERROR: deadlock detected > 1286785 22142 2013-07-26 13:55:25 CEST DETAIL: Process 22142 waits for > ShareLock on transaction > 1286780; blocked by process 22426. > Process 22426 waits for ExclusiveLock on tuple (365,13) of relation 2027300 > of database 2026760; > blocked by process 22142. > 1286785 22142 2013-07-26 13:55:25 CEST HINT: See server log for query > details. > ** > > > If I look in postgresql log for process, I see this (its a web app): > 1.Process 22142: take a ADD request from http, > 2.Process 22426: take a ADD request from http, > 3.Process 22142: do INSERT of new film > 4.Process 22146: do INSERT of new film > 5.Process 22142: continue request (Process 22146 seems to be blocked) and do > COMMIT > 6.Process 22142: take a ADD request from http, > 7.Process 22142: do INSERT of new film > 8.DEADLOCK: process 22142 is waiting for 22146 and 22146 is waiting for 22142 > > I don't understant why the commit of the process 22142 won't unlock process > 22426. > > Have you an idea? It would be interesting to know what relation 2027300 of database 2026760 is. Then you could select the offending tuple with SELECT * FROM WHERE ctid='(365,13)'; What I would do is to set log_statement='all' and see what exact SQL statements are issued. Maybe Hibernate does something you do not know. It may also be interesting to query pg_locks immediately before commit to see what locks one transaction holds. Yours, Laurenz Albe -- 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] How to do incremental / differential backup every hour in Postgres 9.1?
Hi Neil,
Il 26/07/2013 00:24, Neil McGuigan ha scritto:
Trying to do an hourly hot incremental backup of a single postgres
server (windows).
I have the following setup in postgresql.conf:
max_wal_senders=2
wal_level=archive
archive_mode=on
archive_command='copy "%p" "c:\\postgres\\archive\\%f"'
I did a base backup with pg_basebackup -U postgres -D ..\archive -Ft
-l 2013-07-07 -x
Which made a big base.tar file in the archive folder and added some
long file name files, which I assume are the WALs.
Yes, they are WAL files. WAL filename follows a definite format, made by
24 digits.
pg_start_backup('label') and pg_stop_backup() seem to create the WAL
files in xp_log, and then copy them to the archive folder.
Questions:
1. what command(s) do I run to do a new incremental backup
(pg_basebackup does a new base backup which I don't want right now)?
do I just run select pg_start_backup('label'); select
pg_stop_backup(); on a schedule?
Yes, you have to schedule on Windows Scheduler pg_start_backup() and
pg_stop_backup() every time is needed, without doing a base backup.
pg_start_backup() function performs on-line backup and pg_stop_backup()
finishes the performing, meaning that they take care of WAL and backup
copy from the data directory, not of backup creation. So, try to
schedule also a single pg_basebackup to have an updated base backup.
2. What does the label in pg_basebackup and pg_start_backup() do exactly?
The label in pg_start_backup() can be any arbitrary user-defined label.
A good practice is to usethe name under which the backup dump file will
be stored.The label is not used later by any other PostgreSQL command.
3. WAL Files don't seem to be removed from pg_xlog. What should I do
about that? It seems to keep 5 or so WALs in pg_xlog. Is that to be
expected?
Which PostgreSQL version are you using? WALs should be removed
automatically after the archive_command starting from 8.2. Anyway, do a
check in pg_xlog/archive_status/ if it contains a matching
.backup.done file.
4. Do I need to backup the .backup files in the archive folder or just
the 16,384KB WAL files?
The .backup file is very small, and contains some information about the
backup. I'm not sure that it is strictly necessary to move it in the
archive folder, anyway it is so small (<1kB) so it could be lightly copied.
5. should I use the --xlog parameter and if so do I need to change
wal_keep_segments from 0?
You're already using the -x option, this is the reason you're including
WAL files in the backup when you launch pg_basebackup. Notice that WALs
are collected at the end of the backup, so you need to set
wal_keep_segments parameter high enough that the log is not removed
before the end of the backup.
Giuseppe.
--
Giuseppe Broccolo - 2ndQuadrant Italy
PostgreSQL Training, Services and Support
giuseppe.brocc...@2ndquadrant.it | www.2ndQuadrant.it
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Trigger and deadlock
Hello, I've try to make some concurrency robustness test with an web server app that use Hibernate and Postgres. It seems that my trigger make deadlock when multiple thread use it. I will try to simplify examples: I have a table "films"(id, title,director) and a table "directors"(id,name,nbreFilms). I want to automaticaly count directors's films. So I have this triggers after each insert on films: CREATE OR REPLACE FUNCTION incrementDirectors() RETURNS TRIGGER AS $incDirectors$ BEGIN UPDATE directors SET nbreFilm = nbreFilm + 1 WHERE directors.id = NEW.director; RETURN NEW; END; $incDirectors$ LANGUAGE plpgsql; CREATE TRIGGER triggerIncrDirectors AFTER INSERT ON films FOR EACH ROW EXECUTE PROCEDURE incrementDirectors(); When I do a lot of INSERT films at the same time, I have this error: ** 1286785 22142 2013-07-26 13:55:25 CEST ERROR: deadlock detected 1286785 22142 2013-07-26 13:55:25 CEST DETAIL: Process 22142 waits for ShareLock on transaction 1286780; blocked by process 22426. Process 22426 waits for ExclusiveLock on tuple (365,13) of relation 2027300 of database 2026760; blocked by process 22142. 1286785 22142 2013-07-26 13:55:25 CEST HINT: See server log for query details. ** If I look in postgresql log for process, I see this (its a web app): 1.Process 22142: take a ADD request from http, 2.Process 22426: take a ADD request from http, 3.Process 22142: do INSERT of new film 4.Process 22146: do INSERT of new film 5.Process 22142: continue request (Process 22146 seems to be blocked) and do COMMIT 6.Process 22142: take a ADD request from http, 7.Process 22142: do INSERT of new film 8.DEADLOCK: process 22142 is waiting for 22146 and 22146 is waiting for 22142 I don't understant why the commit of the process 22142 won't unlock process 22426. Have you an idea? Thanks :)
[GENERAL] DATE type output does not follow datestyle parameter
Hello, The description of datestyle parameter does not seem to match the actual behavior. Is this a bug to be fixed? Which do you think should be corrected, the program or the manual? The manual says: DateStyle (string) Sets the display format for date and time values, as well as the rules for interpreting ambiguous date input values. For historical reasons, this variable contains two independent components: the output format specification (ISO, Postgres, SQL, or German) and the input/output specification for year/month/day ordering (DMY, MDY, or YMD). ... And says: http://www.postgresql.org/docs/current/static/datatype-datetime.html 8.5.2. Date/Time Output The output of the date and time types is of course only the date or time part in accordance with the given examples. After doing "SET datestyle = 'Postgres, MDY'" on the psql prompt, I did the following things on the same psql session: 1. SELECT current_timestamp; now -- Wed Jul 24 10:51:00.217 2013 GMT (1 行) This is exactly as I expected. 2. SELECT current_date; I expected the output "Wed Jul 24 2013" or "Jul 24 2013", but I got: date 07-24-2013 (1 行) This does not follow the above statement in 8.5.2. This output is created by EncodeDateOnly() in src/backend/utils/adt/datetime.c. Regards MauMau -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: How to do incremental / differential backup every hour in Postgres 9.1?
>5. should I use the --xlog parameter and if so do I need to change wal_keep_segments from 0? you have already use --xlog as -x in your above command of pg_basebackup . yes it better to change wal_keep_segments, if you want start a postmaster directly in the extracted directory without the need to consult the log archive, thus making this a completely standalone backup. Regards, Amul Sul -- View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-do-incremental-differential-backup-every-hour-in-Postgres-9-1-tp5765208p5765249.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] How to do incremental / differential backup every hour in Postgres 9.1?
On Fri, Jul 26, 2013 at 7:24 AM, Neil McGuigan wrote:
> Trying to do an hourly hot incremental backup of a single postgres server
> (windows).
>
> I have the following setup in postgresql.conf:
>
> max_wal_senders=2
> wal_level=archive
> archive_mode=on
> archive_command='copy "%p" "c:\\postgres\\archive\\%f"'
> I did a base backup with pg_basebackup -U postgres -D ..\archive -Ft -l
> 2013-07-07 -x
>
> Which made a big base.tar file in the archive folder and added some long
> file name files, which I assume are the WALs.
>
> pg_start_backup('label') and pg_stop_backup() seem to create the WAL files
> in xp_log, and then copy them to the archive folder.
>
> Questions:
>
> 1. what command(s) do I run to do a new incremental backup (pg_basebackup
> does a new base backup which I don't want right now)? do I just run select
> pg_start_backup('label'); select pg_stop_backup(); on a schedule?
>
pg_start_backup('label') and pg_stop_backup() do not perform actual
copy (full or incremental) of your data. They merely mark the start
and end of a backup operation. Moreover, pg_start_backup() performs
actions (like checkpoint) necessary to prepare a snapshot of your data
(which you would eventually copy/backup) that you can consistently
recover from later.
> 2. What does the label in pg_basebackup and pg_start_backup() do exactly?
>
The label is used to name a file included in the backup directory that
contains information required to recover from a backup.
> 3. WAL Files don't seem to be removed from pg_xlog. What should I do about
> that? It seems to keep 5 or so WALs in pg_xlog. Is that to be expected?
>
You don't need to manually move any of the files in pg_xlog/,
PostgreSQL automatically gets rid of or recycles the files.
> 4. Do I need to backup the .backup files in the archive folder or just the
> 16,384KB WAL files?
>
Your archiving setup (that is archive_command) should take care of all
that needs to be copied to the archive location.
Have you read about pg_rman? Read about it at the following link:
https://code.google.com/p/pg-rman/wiki/readme
--
Amit Langote
--
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] casting tsrange to tstzrange doesn't seem to work?
On Friday, July 5, 2013, Jeff Davis wrote: > On Tue, 2013-06-11 at 14:05 -0700, Joe Van Dyk wrote: > > # select tsrange(null)::tstzrange; > > ERROR: cannot cast type tsrange to tstzrange > > LINE 1: select tsrange(null)::tstzrange; > > > I agree that there should be a cast between tsrange and tstzrange. > > Unfortunately, this cant work generally for all range types, because the > total order might be different. For instance, we can't cast between a > textrange and int4range, because: > >['09','1'] > > is a valid text range, but: > >[9,1] > > is not. > > Regards, > Jeff Davis > Worth it to file a bug for this?
