Re: [GENERAL] Postgres Ubuntu Default Package vs Enterprise DB vs Compiled One
I manage to have the same performance between ubuntu package and compiled version ; just a few seconds of difference. don't know what parameter i've missed i restart again and again ... Sorry to have pollute the mailing list ;) Thank you Adrian for the tips on pg_config and pg_controldata. Good night Sébastien Boutté On Sat, Dec 26, 2015 at 10:34 PM, Sébastien Boutté < sebastien.bou...@gmail.com> wrote: > Honestly, not enough information to make a determination. So: > > 1) Is the order you show above the actual order you did the restores? > No, i have made multiples tests but it's always the same order of nature. > > 2) Are the above timings from one run for each setup or multiple runs? > Multiple runs. > > 3) Just to be clear, this is all on the same machine, correct? Yes > Core i5 2500K > 16G RAM > SSD 256Go > > > Is a problem with : >> User Space / Kernel Space ? >> Compilations options ? >> LD Library ? >> Missing something ? >> > > To see how each installation is set up you can: > > 1) Run the pg_config for each installation, so for instance, on a compiled > version on my machine: > > /usr/local/pgsql/bin/pg_config > > i've attached config files for : > > Ubuntu Default : pgconfig.ubuntu & pg_controldata.ubuntu > Compiled Version : pg_config.compiled & pg_controldata.compiled > > > 2) Run each installations pg_controldata against the installation data > directory: > > /usr/local/pgsql/bin/pg_controldata /usr/local/pgsql/data/ > > > >> >
Re: Fwd: [GENERAL] Postgres Ubuntu Default Package vs Enterprise DB vs Compiled One
On 12/26/2015 01:36 PM, Sébastien Boutté wrote: Honestly, not enough information to make a determination. So: 1) Is the order you show above the actual order you did the restores? No, i have made multiples tests but it's always the same order of nature. 2) Are the above timings from one run for each setup or multiple runs? Multiple runs. 3) Just to be clear, this is all on the same machine, correct? Yes Core i5 2500K 16G RAM SSD 256Go Is a problem with : User Space / Kernel Space ? Compilations options ? LD Library ? Missing something ? To see how each installation is set up you can: 1) Run the pg_config for each installation, so for instance, on a compiled version on my machine: /usr/local/pgsql/bin/pg_config i've attached config files for : Ubuntu Default : pgconfig.ubuntu & pg_controldata.ubuntu Compiled Version : pg_config.compiled & pg_controldata.compiled 2) Run each installations pg_controldata against the installation data directory: /usr/local/pgsql/bin/pg_controldata /usr/local/pgsql/data/ Hmm, the Ubuntu configuration sets a lot of *FLAGS. Unfortunately I am not well versed enough to offer any guidance on whether they matter or not. That will depend on someone more capable than I. So, you are interested in only the Ubuntu and compiled versions of the databases, not the EDB version, or am I missing some attachments? Something else came to mind, when you ran the pg_restore command did you run the one specific to each installation or whichever was first on the PATH? Lastly, are there any errors in the respective Postgres logs that might shed light. I ask because the Ubuntu Postgres is compiled with a lot of extras built in, '--with-python' '--with-pam' '--with-openssl', etc, whereas the self compiled version is not. Just wondering if there are objects in your backup that need those extras? -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Fwd: [GENERAL] Postgres Ubuntu Default Package vs Enterprise DB vs Compiled One
Honestly, not enough information to make a determination. So: 1) Is the order you show above the actual order you did the restores? No, i have made multiples tests but it's always the same order of nature. 2) Are the above timings from one run for each setup or multiple runs? Multiple runs. 3) Just to be clear, this is all on the same machine, correct? Yes Core i5 2500K 16G RAM SSD 256Go Is a problem with : > User Space / Kernel Space ? > Compilations options ? > LD Library ? > Missing something ? > To see how each installation is set up you can: 1) Run the pg_config for each installation, so for instance, on a compiled version on my machine: /usr/local/pgsql/bin/pg_config i've attached config files for : Ubuntu Default : pgconfig.ubuntu & pg_controldata.ubuntu Compiled Version : pg_config.compiled & pg_controldata.compiled 2) Run each installations pg_controldata against the installation data directory: /usr/local/pgsql/bin/pg_controldata /usr/local/pgsql/data/ > pg_config.compiled Description: Binary data pg_config.ubuntu Description: Binary data pg_controldata.compiled Description: Binary data pg_controldata.ubuntu Description: Binary data -- 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] Recurring and non recurring events.
Gavin Flower writes: > The motivation of bottom posting like this: is that people get to see > the context before the reply, AND emails don't end up getting longer & > longer as people reply at the beginning forgetting to trim the now > irrelevant stuff at the end. Of course, this also requires that people have the discipline to trim as much as possible of what they're quoting. Otherwise, not only do the messages get longer and longer anyway, but you have to scroll to the bottom to find what's new. The general rule for proper email quoting is to quote just enough to remind readers what the context is. You are not trying to create a complete archive of the whole thread in every message; we have email archives for that. And the reason why this is worth doing is that it shows respect for your readers' time. I'm not sure how many people look at each message in a popular list like pgsql-general, but surely it's measured in the thousands. If you spend a few minutes judiciously cutting quotes and interspersing your responses in a logical fashion, that may save each reader only a few seconds in reading/understanding your message, but that's still a large net savings of time. 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] Recurring and non recurring events.
Pleas don't top post - see comment at the bottom of this email. On 27/12/15 01:03, Kevin Waterson wrote: Thanks, as I am new to postgres, I was unaware of this function. To go with this, I guess I will need a table with which to store intervals, start and end dates? eg CREATE table events( id serial primary key, start_timestamp timestamp, end_timestamp timestamp, interval with dateRange as ( SELECT min(start_timestamp) as first_date, max(start_timestamp) as last_date FROM events ) select generate_series(first_date, last_date, '1 hour'::interval)::timestamp as date_hour from dateRange; or something?? Kind regards Kevin On Sat, Dec 26, 2015 at 7:22 PM, Pavel Stehule mailto:pavel.steh...@gmail.com>> wrote: Hi 2015-12-26 8:28 GMT+01:00 Kevin Waterson mailto:kevin.water...@gmail.com>>: I wish to set up a table of recurring, and non-recurring events. I have been looking at http://justatheory.com/computers/databases/postgresql/recurring_events.html which looks nice (complex but nice) and wonder if there was a better option for this in more recent pgsql versions. [...] In this list, the convention is to post replies at the end (with some rare exceptions), or interspersed when appropriate, and to omit parts no longer relevant. The motivation of bottom posting like this: is that people get to see the context before the reply, AND emails don't end up getting longer & longer as people reply at the beginning forgetting to trim the now irrelevant stuff at the end. Cheers, Gavin -- 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] Recurring and non recurring events.
On Sat, Dec 26, 2015 at 23:03:30 +1100, Kevin Waterson wrote: Thanks, as I am new to postgres, I was unaware of this function. To go with this, I guess I will need a table with which to store intervals, start and end dates? There is are built in range types that might be more efficiebt for indexing rather than using separate start and stop times. See: http://www.postgresql.org/docs/9.5/static/rangetypes.html#RANGETYPES-BUILTIN -- 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 Ubuntu Default Package vs Enterprise DB vs Compiled One
On 12/26/2015 08:16 AM, Sébastien Boutté wrote: Hi, For my project, to be able to easily manage postgres instance and version, i want to install it in user's directory and launch by user also. For my tests, I've got some issues with pg_restore on three differents installations. First One : Ubuntu 15.10 Default Package Second One : Enterprise Db installer with installation and data files stored in user's home. Third One : Compiled Version Postgres with executable and data files stored in user's home. All versions are 9.4.5. My Backup is : 414 MB (Directory Archive) Postgresql.conf modifications for all cases: shared_buffers = 512MB work_mem = 128MB# min 64kB maintenance_work_mem = 512MB# min 1MB fsync = off# turns forced synchronization on or off synchronous_commit = off# synchronization level; full_page_writes = off# recover from partial page writes wal_buffers = 1024 checkpoint_segments = 64# in logfile segments, min 1, 16MB each checkpoint_timeout = 5min# range 30s-1h checkpoint_completion_target = 0.9# checkpoint target duration, 0.0 - 1.0 checkpoint_warning = 30s# 0 disables Same Command for restore : pg_restore -d -j 4 -F d -U yyy -W backup First One : Restore Time 1min53 Second One : Restore Time 3min48 Third One : Restore Time 2min51 I check files, encoding but i have not found any misconfiguration parameters. What do i have to do to have the same performance in the first and the third ? Honestly, not enough information to make a determination. So: 1) Is the order you show above the actual order you did the restores? 2) Are the above timings from one run for each setup or multiple runs? 3) Just to be clear, this is all on the same machine, correct? Is a problem with : User Space / Kernel Space ? Compilations options ? LD Library ? Missing something ? To see how each installation is set up you can: 1) Run the pg_config for each installation, so for instance, on a compiled version on my machine: /usr/local/pgsql/bin/pg_config 2) Run each installations pg_controldata against the installation data directory: /usr/local/pgsql/bin/pg_controldata /usr/local/pgsql/data/ Thanks you for your help Sebastien Boutte -- Adrian Klaver adrian.kla...@aklaver.com -- 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 Ubuntu Default Package vs Enterprise DB vs Compiled One
Hi, For my project, to be able to easily manage postgres instance and version, i want to install it in user's directory and launch by user also. For my tests, I've got some issues with pg_restore on three differents installations. First One : Ubuntu 15.10 Default Package Second One : Enterprise Db installer with installation and data files stored in user's home. Third One : Compiled Version Postgres with executable and data files stored in user's home. All versions are 9.4.5. My Backup is : 414 MB (Directory Archive) Postgresql.conf modifications for all cases: shared_buffers = 512MB work_mem = 128MB # min 64kB maintenance_work_mem = 512MB # min 1MB fsync = off # turns forced synchronization on or off synchronous_commit = off # synchronization level; full_page_writes = off # recover from partial page writes wal_buffers = 1024 checkpoint_segments = 64 # in logfile segments, min 1, 16MB each checkpoint_timeout = 5min # range 30s-1h checkpoint_completion_target = 0.9 # checkpoint target duration, 0.0 - 1.0 checkpoint_warning = 30s # 0 disables Same Command for restore : pg_restore -d -j 4 -F d -U yyy -W backup First One : Restore Time 1min53 Second One : Restore Time 3min48 Third One : Restore Time 2min51 I check files, encoding but i have not found any misconfiguration parameters. What do i have to do to have the same performance in the first and the third ? Is a problem with : User Space / Kernel Space ? Compilations options ? LD Library ? Missing something ? Thanks you for your help Sebastien Boutte
Re: [GENERAL] Recurring and non recurring events.
Thanks, as I am new to postgres, I was unaware of this function. To go with this, I guess I will need a table with which to store intervals, start and end dates? eg CREATE table events( id serial primary key, start_timestamp timestamp, end_timestamp timestamp, interval with dateRange as ( SELECT min(start_timestamp) as first_date, max(start_timestamp) as last_date FROM events ) select generate_series(first_date, last_date, '1 hour'::interval)::timestamp as date_hour from dateRange; or something?? Kind regards Kevin On Sat, Dec 26, 2015 at 7:22 PM, Pavel Stehule wrote: > Hi > > 2015-12-26 8:28 GMT+01:00 Kevin Waterson : > >> I wish to set up a table of recurring, and non-recurring events. >> I have been looking at >> http://justatheory.com/computers/databases/postgresql/recurring_events.html >> which looks nice (complex but nice) and wonder if there was a better >> option for this in more recent pgsql versions. >> >> All pointers gratefully received. >> > > use generate_series > > postgres=# select v::date from generate_series(current_date, current_date > + 100, interval '7days') g(v); > ┌┐ > │ v │ > ╞╡ > │ 2015-12-26 │ > │ 2016-01-02 │ > │ 2016-01-09 │ > │ 2016-01-16 │ > │ 2016-01-23 │ > │ 2016-01-30 │ > │ 2016-02-06 │ > │ 2016-02-13 │ > │ 2016-02-20 │ > │ 2016-02-27 │ > │ 2016-03-05 │ > │ 2016-03-12 │ > │ 2016-03-19 │ > │ 2016-03-26 │ > │ 2016-04-02 │ > └┘ > (15 rows) > > >> Kev >> > > -- -- "Democracy is two wolves and a lamb voting on what to have for lunch. Liberty is a well-armed lamb contesting the vote."
Re: [GENERAL] BDR and synchronous replication
On 26 December 2015 at 19:29, Nikhil wrote: > Hello, > > i am experimenting BDR project. As BDR does asynchronous replication, i > have a query regarding bdr.synchronous_commit=on option. > > Will aforementioned configuration in postgresql.conf makes the replication > synchronous? > No. It does not make replication synchronous. It makes the BDR downstream apply process commit and flush to disk each transaction it replays as soon as it has replayed it, instead of waiting until it's time to send replay feedback to the upstream. This means there's a pause for fsync with each applied xact, which performs terribly for high transaction rates. By default with synchronous_commit off BDR instead sends confirmation lazily as the transactions actually flush to disk. See bdr_send_feedback in the source code for details. If you want synchronous replication you can use synchronous_standby_names to make replication to exactly one peer server synchronous (ish). You have to set an application_name in the connection dsn on the peer server so you can identify it in synchronous_standby_names. It is not necessary to set bdr.synchronous_commit = on. Doing so may result in quicker confirmation of commit on the upstream at the cost of lower overall performance. There is no way to get n-safe synchronous replication because PostgreSQL doesn't support that, and BDR uses PostgreSQL's synchronous replication support. You can only have one synchronous replica active at a time and PostgreSQL will automatically choose the first reachable one in the replica list. > Does this require any other setting? any side effect for using this setup? > The usual downsides of synchronous replication. A potentially significant performance hit, the upstream stopping when the downstream isn't reachable, etc. Personally I don't think it's a good idea to try to combine BDR and synchronous replication. There are too many pitfalls, especially around the 1-synchronous-replica limitation. It'll be better if/when core gets support for n-safe synchronous replication. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
[GENERAL] BDR and synchronous replication
Hello, i am experimenting BDR project. As BDR does asynchronous replication, i have a query regarding bdr.synchronous_commit=on option. Will aforementioned configuration in postgresql.conf makes the replication synchronous? Does this require any other setting? any side effect for using this setup? Best Regards, Nikhil
Re: [GENERAL] Recurring and non recurring events.
Hi 2015-12-26 8:28 GMT+01:00 Kevin Waterson : > I wish to set up a table of recurring, and non-recurring events. > I have been looking at > http://justatheory.com/computers/databases/postgresql/recurring_events.html > which looks nice (complex but nice) and wonder if there was a better > option for this in more recent pgsql versions. > > All pointers gratefully received. > use generate_series postgres=# select v::date from generate_series(current_date, current_date + 100, interval '7days') g(v); ┌┐ │ v │ ╞╡ │ 2015-12-26 │ │ 2016-01-02 │ │ 2016-01-09 │ │ 2016-01-16 │ │ 2016-01-23 │ │ 2016-01-30 │ │ 2016-02-06 │ │ 2016-02-13 │ │ 2016-02-20 │ │ 2016-02-27 │ │ 2016-03-05 │ │ 2016-03-12 │ │ 2016-03-19 │ │ 2016-03-26 │ │ 2016-04-02 │ └┘ (15 rows) > Kev >
