Re: [GENERAL] BRIN indexes

[Alvaro Herrera]

Melvin Davidson wrote:

> With regard to BRIN indexes:
> 
> http://www.postgresql.org/docs/9.5/interactive/brin-intro.html
> 
> 62.1. Introduction
> 
>   "A block range is a group of pages that are physically adjacent in the
> table; for each block range, some summary info is stored by the index."
> 
> From the above, may I presume that it is best to cluster (or sort), the
> table based on the intended
> BRIN column(s) before actually creating the index to insure the pages are
> adjacent? If so, should
>  that not be included in the documentation, instead of implied?

The issue is that you cannot normally afford to cluster a table every
once in a while; if the natural order in which data is loaded isn't good
for BRIN, then perhaps you shouldn't consider BRIN at all.  If you're
bulk-loading and then create a BRIN index, then it's better to load the
data in order of the columns.  But perhaps you have reasons to have the
table sorted in some other order, in which case trying to satisfy BRIN
would be worse.

All in all, I think there are enough caveats about this that I'm not
sure about putting it up in the doc.

I don't have faith in CLUSTER anyway.  Taking exclusive locks and all.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, 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

Re: [GENERAL] Using a german affix file for compound words

[Oleg Bartunov]

On Thu, Jan 28, 2016 at 6:04 PM, Wolfgang Winkler <
wolfgang.wink...@digital-concepts.com> wrote:

> Hi!
>
> We have a problem with importing a compound dictionary file for german.
>
> I downloaded the files here:
>
>
> http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/dicts/ispell/ispell-german-compound.tar.gz
>
> and converted them to utf-8 with iconv. The affix file seems ok when
> opened with an editor.
>
> When I try to create or alter a dictionary to use this affix file, I get
> the following error:
>
> alter TEXT SEARCH DICTIONARY german_ispell (
>   DictFile = german,
>   AffFile = german,
>   StopWords = german
> );
> ERROR:  syntax error
> CONTEXT:  line 224 of configuration file
> "/usr/local/pgsql/share/tsearch_data/german.affix": "   ABE > -ABE,äBIN
> "
>
> This is the first occurrence of an umlaut character in the file. I've
> found a view postings where the same file is used, e.g.:
>
>
> http://www.postgresql.org/message-id/flat/556c1411.4010...@tbz-pariv.de#556c1411.4010...@tbz-pariv.de
>
> This users has been able to import the file. Am I missing something
> obvious?
>

Arthur Zakirov could help you.


>
> ww
>
>
> --
> 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] Using a german affix file for compound words

[Artur Zakirov]

On 28.01.2016 18:57, Oleg Bartunov wrote:



On Thu, Jan 28, 2016 at 6:04 PM, Wolfgang Winkler
> wrote:

Hi!

We have a problem with importing a compound dictionary file for german.

I downloaded the files here:


http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/dicts/ispell/ispell-german-compound.tar.gz

and converted them to utf-8 with iconv. The affix file seems ok when
opened with an editor.

When I try to create or alter a dictionary to use this affix file, I
get the following error:

alter TEXT SEARCH DICTIONARY german_ispell (
   DictFile = german,
   AffFile = german,
   StopWords = german
);
ERROR:  syntax error
CONTEXT:  line 224 of configuration file
"/usr/local/pgsql/share/tsearch_data/german.affix": "   ABE > -ABE,äBIN
"

This is the first occurrence of an umlaut character in the file.
I've found a view postings where the same file is used, e.g.:


http://www.postgresql.org/message-id/flat/556c1411.4010...@tbz-pariv.de#556c1411.4010...@tbz-pariv.de

This users has been able to import the file. Am I missing something
obvious?



What version of PostgreSQL do you use?

I tested this dictionary on PostgreSQL 9.4.5. Downloaded from the link 
files and executed commands:


iconv -f ISO-8859-1 -t UTF-8 german.aff -o german2.affix
iconv -f ISO-8859-1 -t UTF-8 german.dict -o german2.dict

I renamed them to german.affix and german.dict and moved to the 
tsearch_data directory. Executed commands without errors:


-> create text search dictionary german_ispell (
Template = ispell,
DictFile = german,
AffFile = german,
Stopwords = german
);
DROP TEXT SEARCH DICTIONARY

-> select ts_lexize('german_ispell', 'test');
 ts_lexize
---
 {test}
(1 row)

--
Artur Zakirov
Postgres Professional: http://www.postgrespro.com
Russian Postgres Company


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] BRIN indexes

[Melvin Davidson]

Reposting because I received no reply from a previous inquiry as "9.5 new
features".

With regard to BRIN indexes:

http://www.postgresql.org/docs/9.5/interactive/brin-intro.html

62.1. Introduction

  "A block range is a group of pages that are physically adjacent in the
table; for each block range, some summary info is stored by the index."

>From the above, may I presume that it is best to cluster (or sort), the
table based on the intended
BRIN column(s) before actually creating the index to insure the pages are
adjacent? If so, should
 that not be included in the documentation, instead of implied?

-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Replication Question

[Andreas Kretschmer]

Bala Venkat  wrote:

> Hi there -
> 
>    We have a set up where there is One master streaming to 3 Slaves . 
> 2 slaves are in our DR environment. One is the prod environment. 
> 
>   Wanted to make the DR as primary. I know we can make the one of the
> slave in DR to primary. If I want to keep the other slave as slave connecting
> to the new Master DR , is it possible ? or I have to set the whole streaming
> process again in DR ?  Can you please share your experience ?

should be possible, but you have to change the recovery.conf to point
out to the new master, and, of course, the new master should stream the
wals (wal_sender, proper pg_hba.conf and so on).


Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.  (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°


-- 
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 9.4.5 Installation on Centos 7.3

[Sachin Srivastava]

Dear Adrian,


I have initialized the database through below command through Postgres user
but in my "/u01/postgres9.4/" folder pg_log folder is not available, what
is the reason behind this.

---

-bash-4.2$ initdb -D /u01/postgres9.4/

The files belonging to this database system will be owned by user
"postgres".
This user must also own the server process.
The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
fixing permissions on existing directory /u01/postgres9.4 ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 32MB
creating configuration files ... ok
creating template1 database in /u01/postgres9.4/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects' descriptions ... ok
creating collations ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
loading PL/pgSQL server-side language ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok
WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
Success. You can now start the database server using:
postgres -D /u01/postgres9.4
or
pg_ctl -D /u01/postgres9.4 -l logfile start
-bash-4.2$

-


[root@gdi-test postgres9.4]# pwd
/u01/postgres9.4
[root@gdi-test postgres9.4]# ls -ltr
total 108
drwx-- 2 postgres postgres  4096 Jan 29 11:39 pg_twophase
drwx-- 2 postgres postgres  4096 Jan 29 11:39 pg_tblspc
drwx-- 2 postgres postgres  4096 Jan 29 11:39 pg_stat_tmp
drwx-- 2 postgres postgres  4096 Jan 29 11:39 pg_stat
drwx-- 2 postgres postgres  4096 Jan 29 11:39 pg_snapshots
drwx-- 2 postgres postgres  4096 Jan 29 11:39 pg_serial
drwx-- 2 postgres postgres  4096 Jan 29 11:39 pg_replslot
drwx-- 4 postgres postgres  4096 Jan 29 11:39 pg_multixact
drwx-- 2 postgres postgres  4096 Jan 29 11:39 pg_dynshmem
-rw--- 1 postgres postgres 4 Jan 29 11:39 PG_VERSION
drwx-- 4 postgres postgres  4096 Jan 29 11:39 pg_logical
-rw--- 1 postgres postgres 21307 Jan 29 11:39 postgresql.conf
-rw--- 1 postgres postgres88 Jan 29 11:39 postgresql.auto.conf
-rw--- 1 postgres postgres  1636 Jan 29 11:39 pg_ident.conf
-rw--- 1 postgres postgres  4468 Jan 29 11:39 pg_hba.conf
drwx-- 3 postgres postgres  4096 Jan 29 11:39 pg_xlog
drwx-- 2 postgres postgres  4096 Jan 29 11:39 pg_subtrans
drwx-- 2 postgres postgres  4096 Jan 29 11:39 pg_clog
drwx-- 2 postgres postgres  4096 Jan 29 11:39 pg_notify
drwx-- 2 postgres postgres  4096 Jan 29 11:39 global
drwx-- 5 postgres postgres  4096 Jan 29 11:39 base
[root@gdi-test postgres9.4]#

---

Regards
SS



On Fri, Jan 29, 2016 at 11:33 AM, Adrian Klaver 
wrote:

> On 01/28/2016 08:45 PM, Sachin Srivastava wrote:
>
>> Hi,
>>
>> I have to install Postgres 9.4.5 On Centos 7.2, what is the best way to
>> install, please suggest:
>>
>>
>> Through ./configure and make
>>
>> OR
>>
>> Through below link:
>>
>>
>> https://wiki.postgresql.org/wiki/YUM_Installation#Configure_your_YUM_repository
>>
>>
>> Please provide, if any other installation document.
>>
>
> I would say using the Yum repo. If you do the compile from source you will
> need to make sure you have a development tool chain in place as well as
> --devel libraries for the various components.
>
>
>
>>
>> For  Postgres 9.1 version, I did postgres installation through run file
>> like  (postgresql-9.1.2-1-linux-x64.run), for 9.4 version there is no
>> run file right now.
>>
>>
>> Regards
>> SS
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: [GENERAL] Postgres 9.4.5 Installation on Centos 7.3

[John R Pierce]

On 1/28/2016 10:24 PM, Sachin Srivastava wrote:
I have initialized the database through below command through Postgres 
user but in my "/u01/postgres9.4/" folder pg_log folder is not 
available, what is the reason behind this.


---

-bash-4.2$ initdb -D /u01/postgres9.4/



the yum installed version defaults to putting the data directory in 
/var/lib/pgsql/9.4/data ... There's a bunch of reasons to stick with 
this, not the least thats where selinux is configured to expect it to be.


I would recommend mounting a database filesystem as /var/lib/pgsql 
first, then doing the following as root...


/usr/pgsql-9.4/bin/postgresql94-setup initdb
systemctl enable postgresql-9.4.service
systemctl start postgresql-9.4.service

the server is now running, and will autostart when you reboot.

see 
http://people.planetpostgresql.org/devrim/index.php?/archives/80-Installing-and-configuring-PostgreSQL-9.3-and-9.4-on-RHEL-7.html





--
john r pierce, recycling bits in santa cruz



--
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 9.4.5 Installation on Centos 7.3

[Sachin Srivastava]

Dear John,

Thanks for update !!!

Is it not possible If my storage is mounted on /u01 and I will create the
directory  within "u01/"  /postgres9.4/data. And now I want to initialize
my data within this directory ("/u01/postgres9.4/data").

There is any specific reason to use only this by default directory
"/var/lib/pgsql/9.4/data".

And if I want to keep (("/u01/postgres9.4/data) this directory then what
should be the command of initdb because command giving the error.

(-bash-4.2$ /usr/pgsql-9.4/bin/postgresql94-setup initdb -D
/u01/postgres9.4/data/


#cd

Filesystem   Size  Used Avail Use% Mounted on
/dev/mapper/centos-root  116G  664M  109G   1% /
devtmpfs 1.8G 0  1.8G   0% /dev
tmpfs1.8G 0  1.8G   0% /dev/shm
tmpfs1.8G  8.8M  1.8G   1% /run
tmpfs1.8G 0  1.8G   0% /sys/fs/cgroup
/dev/mapper/centos-usr20G  3.2G   16G  17% /usr
/dev/sda1477M  273M  175M  62% /boot
/dev/mapper/centos-var20G  907M   18G   5% /var
*/dev/mapper/centos-u01   296G  2.4G  279G   1% /u01*

cd /u01
mkdir -p /postgres9.4/data

Regards
SS


On Fri, Jan 29, 2016 at 12:34 PM, John R Pierce  wrote:

> On 1/28/2016 10:24 PM, Sachin Srivastava wrote:
>
>> I have initialized the database through below command through Postgres
>> user but in my "/u01/postgres9.4/" folder pg_log folder is not available,
>> what is the reason behind this.
>>
>> ---
>>
>> -bash-4.2$ initdb -D /u01/postgres9.4/
>>
>
>
> the yum installed version defaults to putting the data directory in
> /var/lib/pgsql/9.4/data ... There's a bunch of reasons to stick with this,
> not the least thats where selinux is configured to expect it to be.
>
> I would recommend mounting a database filesystem as /var/lib/pgsql first,
> then doing the following as root...
>
> /usr/pgsql-9.4/bin/postgresql94-setup initdb
> systemctl enable postgresql-9.4.service
> systemctl start postgresql-9.4.service
>
> the server is now running, and will autostart when you reboot.
>
> see
> http://people.planetpostgresql.org/devrim/index.php?/archives/80-Installing-and-configuring-PostgreSQL-9.3-and-9.4-on-RHEL-7.html
>
>
>
>
> --
> john r pierce, recycling bits in santa cruz
>
>
>
> --
> 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 9.4.5 Installation on Centos 7.3

[Sachin Srivastava]

Here selinux is already disabled on server and I am installing postgres on
Centos 7.2.


On Fri, Jan 29, 2016 at 1:12 PM, John R Pierce  wrote:

> On 1/28/2016 11:21 PM, Sachin Srivastava wrote:
>
>>
>> Is it not possible If my storage is mounted on /u01 and I will create the
>> directory  within "u01/"  /postgres9.4/data. And now I want to initialize
>> my data within this directory ("/u01/postgres9.4/data").
>>
>> There is any specific reason to use only this by default directory
>> "/var/lib/pgsql/9.4/data".
>>
>
> you will need to mess with selinux tags to get it to cooperate.
>
>
>> And if I want to keep (("/u01/postgres9.4/data) this directory then what
>> should be the command of initdb because command giving the error.
>>
>
> create file /etc/sysconfig/pgsql/postgresql-9.4 and add the line
> PGDATA=/u01/postgres9.4/data then run the postgreql94-setup initdb, it
> will pick up this setting and use it, as will the systemd service scripts.
>
>
>
>
> --
> john r pierce, recycling bits in santa cruz
>
>
>
> --
> 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 9.4.5 Installation on Centos 7.3

[Adrian Klaver]

On 01/28/2016 08:45 PM, Sachin Srivastava wrote:

Hi,

I have to install Postgres 9.4.5 On Centos 7.2, what is the best way to
install, please suggest:


Through ./configure and make

OR

Through below link:

https://wiki.postgresql.org/wiki/YUM_Installation#Configure_your_YUM_repository


Please provide, if any other installation document.


I would say using the Yum repo. If you do the compile from source you 
will need to make sure you have a development tool chain in place as 
well as --devel libraries for the various components.





For  Postgres 9.1 version, I did postgres installation through run file
like  (postgresql-9.1.2-1-linux-x64.run), for 9.4 version there is no
run file right now.


Regards
SS



--
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

Re: [GENERAL] Postgres 9.4.5 Installation on Centos 7.3

[John R Pierce]

On 1/28/2016 11:21 PM, Sachin Srivastava wrote:


Is it not possible If my storage is mounted on /u01 and I will create 
the directory  within "u01/"  /postgres9.4/data. And now I want to 
initialize my data within this directory ("/u01/postgres9.4/data").


There is any specific reason to use only this by default directory 
"/var/lib/pgsql/9.4/data".


you will need to mess with selinux tags to get it to cooperate.



And if I want to keep (("/u01/postgres9.4/data) this directory then 
what should be the command of initdb because command giving the error.


create file /etc/sysconfig/pgsql/postgresql-9.4 and add the line 
PGDATA=/u01/postgres9.4/data then run the postgreql94-setup initdb, 
it will pick up this setting and use it, as will the systemd service 
scripts.




--
john r pierce, recycling bits in santa cruz



--
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] Request - repeat value of \pset title during \watch interations

[David G. Johnston]

On Thu, Jan 28, 2016 at 6:01 AM, Michael Paquier 
wrote:

> On Thu, Jan 28, 2016 at 1:54 PM, David G. Johnston
>  wrote:
> > On Wed, Jan 27, 2016 at 9:13 PM, Michael Paquier <
> michael.paqu...@gmail.com>
> > wrote:
> >>
> >> On Thu, Jan 28, 2016 at 9:34 AM, David G. Johnston
> >>  wrote:
> >> > So how about:
> >> >
> >> > + snprintf(title, strlen(myopt.title) + 50,
> >> > + _("Watch every %lds\t%s\t%s"),
> >> > +  sleep, head_title, asctime(localtime()));
> >>
> >> I would just keep the timestamp and the title separated so what do you
> >> think about that instead?
> >> Watch every Xs   $timestamp
> >> $head_title
> >
> >
> > That works.  I like having the title immediately above the table.
> >
> > The other option that came to mind would be to place the time information
> > after the table display while leaving the title before it.  On an output
> > that requires more vertical space than is available in the terminal one
> > would no longer have to scroll up to confirm last execution time.  If
> doing
> > this I'd probably get rid of any logic that attempts to center the time
> > information on the table and simply leave it left-aligned.
>
> OK, attached is an updated patch. How does that look?
>
>
Could you run the following and post the result, please?  I have yet to
setup a compiled-from-source installation...

\pset title 'This is a medium length title'
SELECT repeat('a', 100) \watch 5

It seems fine though I'm curious how it plays with various combinations of
narrow and wide outputs on narrow and wide terminals - specifically the
centering​

​aspect that occurs when the table is wider than the Watch/Time content.
The terminal width should just cause wrapping relative to a wide-enough
terminal so really it just the centering aspect when the table is wider
than the header.

​Thanks!

David J.​

Re: [GENERAL] BRIN indexes

[Felipe Santos]

2016-01-28 16:03 GMT-02:00 Joshua D. Drake :

> On 01/28/2016 09:41 AM, Melvin Davidson wrote:
>
>> So, IOW, and the answer to my question is yes, it should be insured that
>> all pages involved are physically adjacent (by design or by pre-sort)
>> before creating a BRIN on them.
>> Further to the point, it is self defeating to have more than one BRIN
>> index on the table if the columns involved would have mutually
>> non-adjacent pages.
>> Therefore, it actually would be good to state that in the documentation,
>> even it were just a comment.
>>
>
> BRIN indexes are best used on INSERT only tables with a sequence of
> numbers as a PK or indexed column that will be queried against. At least as
> I understand it.
>
> JD
>
> --
> Command Prompt, Inc.  http://the.postgres.company/
>  +1-503-667-4564
> PostgreSQL Centered full stack support, consulting and development.
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>


"Further to the point, it is self defeating to have more than one BRIN
index on the table if the columns involved would have mutually
non-adjacent pages."

   Not really, if both columns are ordered, BRIN will work

"Therefore, it actually would be good to state that in the documentation,
even it were just a comment."

   It is = "BRIN is designed for handling very large tables in which
certain columns have some natural correlation with their physical location
within the table"
   Link: http://www.postgresql.org/docs/devel/static/brin-intro.html


Also, I did some tests and here are the results I got:

Query with no index = completion time 43s
Same Query with BRIN = completion time 14s / index size 0,5 MB
Same Query without BRIN and with BTREE = completion time 10s / index size
5.000,00 MB

As you can see, BRIN can save 99% of disk space for just a slightly worse
performance.

It seems like a huge improvement, given that your data fits BRIN's use case.

[GENERAL] Booking Dates and times

[Kevin Waterson]

I am creating a small booking system, and need to generate a series of
dates, for the year, with each booking. Using generate_series I can create
the dates, and fill them with a booking date based on the lower tsrange for
the booking.
I need to extend this to also fetch ALL the dates in the tsrange.

This is the schema and story so far.
All help gratefully welcomed.

http://pastie.org/10698843

Thanks,
Kev

[GENERAL] repmgr quickstart...

[John R Pierce]

I go to read the repmgr quickstart and get a 404?
https://github.com/2ndQuadrant/repmgr/blob/master/QUICKSTART.md

I was on this page yesterday and it was fine.

--
john r pierce, recycling bits in santa cruz



--
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] Using a german affix file for compound words

[Wolfgang Winkler]

I'm using 9.4.5 as well and I used exactly the same iconv lines as you 
postes below.


Are there any encoding options that have to be set right? The database 
encoding is set to UTF8.


ww

Am 2016-01-28 um 17:34 schrieb Artur Zakirov:

On 28.01.2016 18:57, Oleg Bartunov wrote:



On Thu, Jan 28, 2016 at 6:04 PM, Wolfgang Winkler
> wrote:

Hi!

We have a problem with importing a compound dictionary file for 
german.


I downloaded the files here:

http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/dicts/ispell/ispell-german-compound.tar.gz

and converted them to utf-8 with iconv. The affix file seems ok when
opened with an editor.

When I try to create or alter a dictionary to use this affix file, I
get the following error:

alter TEXT SEARCH DICTIONARY german_ispell (
   DictFile = german,
   AffFile = german,
   StopWords = german
);
ERROR:  syntax error
CONTEXT:  line 224 of configuration file
"/usr/local/pgsql/share/tsearch_data/german.affix": "   ABE > 
-ABE,äBIN

"

This is the first occurrence of an umlaut character in the file.
I've found a view postings where the same file is used, e.g.:

http://www.postgresql.org/message-id/flat/556c1411.4010...@tbz-pariv.de#556c1411.4010...@tbz-pariv.de

This users has been able to import the file. Am I missing something
obvious?



What version of PostgreSQL do you use?

I tested this dictionary on PostgreSQL 9.4.5. Downloaded from the link 
files and executed commands:


iconv -f ISO-8859-1 -t UTF-8 german.aff -o german2.affix
iconv -f ISO-8859-1 -t UTF-8 german.dict -o german2.dict

I renamed them to german.affix and german.dict and moved to the 
tsearch_data directory. Executed commands without errors:


-> create text search dictionary german_ispell (
Template = ispell,
DictFile = german,
AffFile = german,
Stopwords = german
);
DROP TEXT SEARCH DICTIONARY

-> select ts_lexize('german_ispell', 'test');
 ts_lexize
---
 {test}
(1 row)




--

*Wolfgang Winkler*
Geschäftsführung
wolfgang.wink...@digital-concepts.com
mobil +43.699.19971172

dc:*büro*
digital concepts Novak Winkler OG
Software & Design
Landstraße 68, 5. Stock, 4020 Linz
www.digital-concepts.com 
tel +43.732.997117.72
tel +43.699.1997117.2

Firmenbuchnummer: 192003h
Firmenbuchgericht: Landesgericht Linz

Re: [GENERAL] BRIN indexes

[Melvin Davidson]

So, IOW, and the answer to my question is yes, it should be insured that
all pages involved are physically adjacent (by design or by pre-sort)
before creating a BRIN on them.
Further to the point, it is self defeating to have more than one BRIN index
on the table if the columns involved would have mutually  non-adjacent
pages.
Therefore, it actually would be good to state that in the documentation,
even it were just a comment.

On Thu, Jan 28, 2016 at 12:31 PM, David Rowley  wrote:

> On 29 January 2016 at 06:10, Melvin Davidson  wrote:
> > With regard to BRIN indexes:
> >
> > http://www.postgresql.org/docs/9.5/interactive/brin-intro.html
> >
> > 62.1. Introduction
> > 
> >   "A block range is a group of pages that are physically adjacent in the
> table; for each block range, some summary info is stored by the index."
> >
> > From the above, may I presume that it is best to cluster (or sort), the
> table based on the intended
> > BRIN column(s) before actually creating the index to insure the pages
> are adjacent? If so, should
> >  that not be included in the documentation, instead of implied?
>
> I personally think the second sentence of the link to the
> documentation covers this quite well. Namely "BRIN is designed for
> handling very large tables in which certain columns have some natural
> correlation with their physical location within the table."
>
> Examples of this might be something like an "orders" table, where you
> have an orderdate column, probably you'll insert into this table as
> orders are received, so quite possibly the table will be naturally
> ordered in ascending orderdate order. Although UPDATEs might create
> new tuples in some free space elsewhere in the relation, but it's not
> hard to imagine other cases where there's no updates and "natural
> correlation" is persisted.
>
> --
> David Rowley   http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Training & Services
>



-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

[GENERAL] pg_dump streaming fails - PostgreSQL 9.2

[drum.lu...@gmail.com]

Hi there!

I'm running this command: *(Trying to copy a full DB (2TB) from a
hot-standby server to a master (test) server)*

ssh postgres@myslaveserver "/usr/pgsql-9.2/bin/pg_dump
--exclude-table-data='junk.*' -—format=custom master_db" |
/usr/pgsql-9.2/bin/pg_restore --dbname=master_db_temp --exit-on-error
—-verbose

Then, after 3 GB I got this error:

pg_dump: Dumping the contents of table "invoices" failed: PQgetResult() failed.
pg_dump: Error message from server: ERROR:  canceling statement due to
conflict with recovery
DETAIL:  User was holding a relation lock for too long.
pg_dump: The command was: COPY dm.invoices (invoice_id,
format_version, ts_issue, ts_ack, customer_id, code, tag,
account_data, customer_data, invoice_data, invoice_items_data) TO
stdout;

I've tried the pg_dump command even with: "--no-unlogged-table-data"
option, but it was unsuccessful (
http://www.postgresql.org/message-id/e1tutdo-0001hb...@wrigleys.postgresql.org
)

DB size: 2 TB

How can I solve the problem? What's going on? Thanks

Re: [GENERAL] pg_dump streaming fails - PostgreSQL 9.2

[Joshua D. Drake]

On 01/28/2016 05:23 PM, drum.lu...@gmail.com wrote:

Hi there!

I'm running this command: *(Trying to copy a full DB (2TB) from a
hot-standby server to a master (test) server)*

|ssh postgres@myslaveserver "/usr/pgsql-9.2/bin/pg_dump
--exclude-table-data='junk.*' -—format=custom
master_db"|/usr/pgsql-9.2/bin/pg_restore --dbname=master_db_temp
--exit-on-error —-verbose|

Then, after 3 GB I got this error:

|pg_dump:Dumping the contents
oftable"invoices"failed:PQgetResult()failed.pg_dump:Error message
fromserver:ERROR:canceling statement due toconflict withrecovery
DETAIL:Userwas holding a relation lock fortoo long.pg_dump:The command
was:COPY dm.invoices
(invoice_id,format_version,ts_issue,ts_ack,customer_id,code,tag,account_data,customer_data,invoice_data,invoice_items_data)TOstdout;|

I've tried the pg_dump command even with: "--no-unlogged-table-data"
option, but it was unsuccessful

(http://www.postgresql.org/message-id/e1tutdo-0001hb...@wrigleys.postgresql.org)

DB size: 2 TB

How can I solve the problem? What's going on? Thanks



Back up everything from the master.

JD



--
Command Prompt, Inc.  http://the.postgres.company/
 +1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.


--
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] Difference between UDR and BDR replication

[Craig Ringer]

On 28 January 2016 at 21:16, Kaushal Shriyan 
wrote:

>
>
> On Thu, Jan 28, 2016 at 6:32 PM, Craig Ringer 
> wrote:
>
>> On 28 January 2016 at 19:16, Kaushal Shriyan 
>> wrote:
>>
>>> Hi,
>>>
>>> Can somebody please help me understand the difference between UDR and
>>> BDR with examples?
>>>
>>
>>
>> BDR is for multiple masters that all replicate to each other.
>>
>> UDR takes data from one server and copies it to another. One way. (By the
>> way, I strongly advise you to now use pglogical instead of UDR).
>>
>> BDR:
>>
>>   A <==> B
>>
>> UDR/pglogical:
>>
>>   A ==> B
>>
>
> Hi Craig,
>
> Thanks for the explanation. Does it mean UDR is Master to Slave
> replication?
>
>
Correct. Please use either PostgreSQL's built-in streaming replication
features or pglogical instead, though.  If you're not sure which, use the
built-in replication features.

-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Re: [GENERAL] Difference between UDR and BDR replication

[Craig Ringer]

On 28 January 2016 at 19:16, Kaushal Shriyan 
wrote:

> Hi,
>
> Can somebody please help me understand the difference between UDR and BDR
> with examples?
>


BDR is for multiple masters that all replicate to each other.

UDR takes data from one server and copies it to another. One way. (By the
way, I strongly advise you to now use pglogical instead of UDR).

BDR:

  A <==> B

UDR/pglogical:

  A ==> B


-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Re: [GENERAL] Difference between UDR and BDR replication

[Kaushal Shriyan]

On Thu, Jan 28, 2016 at 6:32 PM, Craig Ringer  wrote:

> On 28 January 2016 at 19:16, Kaushal Shriyan 
> wrote:
>
>> Hi,
>>
>> Can somebody please help me understand the difference between UDR and BDR
>> with examples?
>>
>
>
> BDR is for multiple masters that all replicate to each other.
>
> UDR takes data from one server and copies it to another. One way. (By the
> way, I strongly advise you to now use pglogical instead of UDR).
>
> BDR:
>
>   A <==> B
>
> UDR/pglogical:
>
>   A ==> B
>

Hi Craig,

Thanks for the explanation. Does it mean UDR is Master to Slave
replication?

Regards,

Kaushal

Re: [GENERAL] Request - repeat value of \pset title during \watch interations

[Michael Paquier]

On Thu, Jan 28, 2016 at 1:54 PM, David G. Johnston
 wrote:
> On Wed, Jan 27, 2016 at 9:13 PM, Michael Paquier 
> wrote:
>>
>> On Thu, Jan 28, 2016 at 9:34 AM, David G. Johnston
>>  wrote:
>> > So how about:
>> >
>> > + snprintf(title, strlen(myopt.title) + 50,
>> > + _("Watch every %lds\t%s\t%s"),
>> > +  sleep, head_title, asctime(localtime()));
>>
>> I would just keep the timestamp and the title separated so what do you
>> think about that instead?
>> Watch every Xs   $timestamp
>> $head_title
>
>
> That works.  I like having the title immediately above the table.
>
> The other option that came to mind would be to place the time information
> after the table display while leaving the title before it.  On an output
> that requires more vertical space than is available in the terminal one
> would no longer have to scroll up to confirm last execution time.  If doing
> this I'd probably get rid of any logic that attempts to center the time
> information on the table and simply leave it left-aligned.

OK, attached is an updated patch. How does that look?
-- 
Michael
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 9750a5b..3241d27 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -3020,7 +3020,8 @@ static bool
 do_watch(PQExpBuffer query_buf, long sleep)
 {
 	printQueryOpt myopt = pset.popt;
-	char		title[50];
+	char		 *title;
+	bool		 *head_title = NULL;
 
 	if (!query_buf || query_buf->len <= 0)
 	{
@@ -3034,6 +3035,18 @@ do_watch(PQExpBuffer query_buf, long sleep)
 	 */
 	myopt.topt.pager = 0;
 
+	/*
+	 * Take into account any title present in the user setup as a part of
+	 * what is printed for each iteration by using it as a header.
+	 */
+	if (myopt.title)
+	{
+		title = pg_malloc(strlen(myopt.title) + 50);
+		head_title = pg_strdup(myopt.title);
+	}
+	else
+		title = pg_malloc(50);
+
 	for (;;)
 	{
 		int			res;
@@ -3045,8 +3058,13 @@ do_watch(PQExpBuffer query_buf, long sleep)
 		 * of completion of the command?
 		 */
 		timer = time(NULL);
-		snprintf(title, sizeof(title), _("Watch every %lds\t%s"),
- sleep, asctime(localtime()));
+		if (head_title)
+			snprintf(title, strlen(myopt.title) + 50,
+	 _("Watch every %lds\t%s\n%s"),
+	 sleep, asctime(localtime()), head_title);
+		else
+			snprintf(title, 50, _("Watch every %lds\t%s"),
+	 sleep, asctime(localtime()));
 		myopt.title = title;
 
 		/* Run the query and print out the results */
@@ -3059,7 +3077,11 @@ do_watch(PQExpBuffer query_buf, long sleep)
 		if (res == 0)
 			break;
 		if (res == -1)
+		{
+			pg_free(title);
+			pg_free(head_title);
 			return false;
+		}
 
 		/*
 		 * Set up cancellation of 'watch' via SIGINT.  We redo this each time
@@ -3084,6 +3106,8 @@ do_watch(PQExpBuffer query_buf, long sleep)
 		sigint_interrupt_enabled = false;
 	}
 
+	pg_free(title);
+	pg_free(head_title);
 	return true;
 }
 

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Difference between UDR and BDR replication

[Kaushal Shriyan]

Hi,

Can somebody please help me understand the difference between UDR and BDR
with examples?

Thanks in Advance.

Regards,

Kaushal

Re: [GENERAL] BRIN indexes

[David Rowley]

On 29 January 2016 at 06:10, Melvin Davidson  wrote:
> With regard to BRIN indexes:
>
> http://www.postgresql.org/docs/9.5/interactive/brin-intro.html
>
> 62.1. Introduction
> 
>   "A block range is a group of pages that are physically adjacent in the 
> table; for each block range, some summary info is stored by the index."
>
> From the above, may I presume that it is best to cluster (or sort), the table 
> based on the intended
> BRIN column(s) before actually creating the index to insure the pages are 
> adjacent? If so, should
>  that not be included in the documentation, instead of implied?

I personally think the second sentence of the link to the
documentation covers this quite well. Namely "BRIN is designed for
handling very large tables in which certain columns have some natural
correlation with their physical location within the table."

Examples of this might be something like an "orders" table, where you
have an orderdate column, probably you'll insert into this table as
orders are received, so quite possibly the table will be naturally
ordered in ascending orderdate order. Although UPDATEs might create
new tuples in some free space elsewhere in the relation, but it's not
hard to imagine other cases where there's no updates and "natural
correlation" is persisted.

-- 
David Rowley   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

Re: [GENERAL] BRIN indexes

[Joshua D. Drake]

On 01/28/2016 09:41 AM, Melvin Davidson wrote:

So, IOW, and the answer to my question is yes, it should be insured that
all pages involved are physically adjacent (by design or by pre-sort)
before creating a BRIN on them.
Further to the point, it is self defeating to have more than one BRIN
index on the table if the columns involved would have mutually
non-adjacent pages.
Therefore, it actually would be good to state that in the documentation,
even it were just a comment.


BRIN indexes are best used on INSERT only tables with a sequence of 
numbers as a PK or indexed column that will be queried against. At least 
as I understand it.


JD

--
Command Prompt, Inc.  http://the.postgres.company/
 +1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.


--
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] Request - repeat value of \pset title during \watch interations

[Michael Paquier]

On Fri, Jan 29, 2016 at 1:55 AM, David G. Johnston wrote:
> Could you run the following and post the result, please?  I have yet to
> setup a compiled-from-source installation...
>
> \pset title 'This is a medium length title'
> SELECT repeat('a', 100) \watch 5
>
> It seems fine though I'm curious how it plays with various combinations of
> narrow and wide outputs on narrow and wide terminals - specifically the
> centering
>
> aspect that occurs when the table is wider than the Watch/Time content.
The
> terminal width should just cause wrapping relative to a wide-enough
terminal
> so really it just the centering aspect when the table is wider than the
> header.

No problem. Here you go:

   Watch every 5sFri Jan 29 13:06:31 2016

This is a medium length title
repeat

--
 

(1 row)
-- 
Michael

[GENERAL] Postgres 9.4.5 Installation on Centos 7.3

[Sachin Srivastava]

Hi,

I have to install Postgres 9.4.5 On Centos 7.2, what is the best way to
install, please suggest:


Through ./configure and make

OR

Through below link:

https://wiki.postgresql.org/wiki/YUM_Installation#Configure_your_YUM_repository


Please provide, if any other installation document.


For  Postgres 9.1 version, I did postgres installation through run file
like  (postgresql-9.1.2-1-linux-x64.run), for 9.4 version there is no run
file right now.


Regards
SS

[GENERAL] Replication Question

[Bala Venkat]

Hi there -

   We have a set up where there is One master streaming to 3 Slaves
.  2 slaves are in our DR environment. One is the prod environment.

  Wanted to make the DR as primary. I know we can make the one of
the slave in DR to primary. If I want to keep the other slave as slave
connecting to the new Master DR , is it possible ? or I have to set the
whole streaming process again in DR ?  Can you please share your experience
?


Regards

Re: [GENERAL] Difference between UDR and BDR replication

[Kaushal Shriyan]

On 28 Jan 2016 6:51 pm, "Craig Ringer"  wrote:
>
> On 28 January 2016 at 21:16, Kaushal Shriyan 
wrote:
>>
>>
>>
>> On Thu, Jan 28, 2016 at 6:32 PM, Craig Ringer 
wrote:
>>>
>>> On 28 January 2016 at 19:16, Kaushal Shriyan 
wrote:

 Hi,

 Can somebody please help me understand the difference between UDR and
BDR with examples?
>>>
>>>
>>>
>>> BDR is for multiple masters that all replicate to each other.
>>>
>>> UDR takes data from one server and copies it to another. One way. (By
the way, I strongly advise you to now use pglogical instead of UDR).
>>>
>>> BDR:
>>>
>>>   A <==> B
>>>
>>> UDR/pglogical:
>>>
>>>   A ==> B
>>
>>
>> Hi Craig,
>>
>> Thanks for the explanation. Does it mean UDR is Master to Slave
replication?
>>
>
> Correct. Please use either PostgreSQL's built-in streaming replication
features or pglogical instead, though.  If you're not sure which, use the
built-in replication features.
>
> --
>  Craig Ringer   http://www.2ndQuadrant.com/
>  PostgreSQL Development, 24x7 Support, Training & Services

Hi Craig,

Thanks a lot for all the help and really appreciated. I am still reading
and will ask here if I have any questions.

Regards,

Kaushal

[GENERAL] Using a german affix file for compound words

[Wolfgang Winkler]

Hi!

We have a problem with importing a compound dictionary file for german.

I downloaded the files here:

http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/dicts/ispell/ispell-german-compound.tar.gz

and converted them to utf-8 with iconv. The affix file seems ok when 
opened with an editor.


When I try to create or alter a dictionary to use this affix file, I get 
the following error:


alter TEXT SEARCH DICTIONARY german_ispell (
  DictFile = german,
  AffFile = german,
  StopWords = german
);
ERROR:  syntax error
CONTEXT:  line 224 of configuration file 
"/usr/local/pgsql/share/tsearch_data/german.affix": "   ABE > -ABE,äBIN

"

This is the first occurrence of an umlaut character in the file. I've 
found a view postings where the same file is used, e.g.:


http://www.postgresql.org/message-id/flat/556c1411.4010...@tbz-pariv.de#556c1411.4010...@tbz-pariv.de

This users has been able to import the file. Am I missing something obvious?

ww


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general