Re: [GENERAL] Unicode license compatibility with PostgreSQL license

2015-03-19 Thread Michael Paquier
On Thu, Mar 19, 2015 at 3:03 PM, Haribabu Kommi wrote: > For our next set of development activities in PostgreSQL, we want to > use the Unicode organization code with PostgreSQL to open source that > feature. Is the Unicode license is compatible with PostgreSQL. > > The following is the header tha

Re: [GENERAL] Reg: PostgreSQL Server "base/data" recovery

2015-03-19 Thread Michael Paquier
On Thu, Mar 19, 2015 at 5:19 PM, Prajilal KP wrote: > Recently i have noticed that my PostgreSQL server is returning the error > messages like "Could not read block 6160722 in file > "base/data/16384/11033028.47". Don't you have more details to share? There should be some hint here as well, and u

Re: [GENERAL] Reg: PostgreSQL Server "base/data" recovery

2015-03-19 Thread Michael Paquier
On Thu, Mar 19, 2015 at 8:12 PM, Prajilal KP wrote: > I see, As there are many reasons exists for the data corruption i couldn't > figure out it exactly. > Unfortunately there is no backup for this system and is not possible to > restore from the backup. Well, then, lost data is lost. > I have b

Re: [GENERAL] create table if not exists xx AS select ...

2015-03-22 Thread Michael Paquier
On Mon, Mar 23, 2015 at 10:28 AM, Daniel Caldeweyher wrote: > I am not entirely sure if this is a bug (fails on 9.3 and 9.4) or working as > intended: > [...] > create table test2 as > select * from test where 1=0; This works as intended, CREATE TABLE AS does not support IF NOT EXISTS in 9.4 and

Re: [GENERAL] reload shared_preload_libraries

2015-03-25 Thread Michael Paquier
On Wed, Mar 25, 2015 at 7:03 PM, Sreerama Manoj wrote: > Is there a way to reload shared_preload_libraries with out restarting the > server?? > > I tried alter system command in postgres 9.4 but I could not change it.. Server needs to be restarted, there is no other way to have its new value take

Re: [GENERAL] How to recover or resent the password for the user 'postgres'

2015-03-30 Thread Michael Paquier
On Mon, Mar 30, 2015 at 5:09 PM, Arup Rakshit wrote: > Hi, > > I am trying to follow what has been mentioned below **Setting Up > Postgres** ( > https://www.digitalocean.com/community/tutorials/how-to-setup-ruby-on-rails-with-postgres > ). > > But no luck! > > [shreyas@myapp(add_postgres_addapter

Re: [GENERAL] JSON TO POSTGRE TABLE

2015-03-30 Thread Michael Paquier
On Mon, Mar 30, 2015 at 6:54 PM, Kavi wrote: > > I sample data as below- > > > { > "glossary": { > "title": "example glossary", > "GlossDiv": { > "title": "S", > "GlossList": { > "GlossEntry": { > "ID

Re: [GENERAL] JSON TO POSTGRE TABLE

2015-03-30 Thread Michael Paquier
On Mon, Mar 30, 2015 at 7:11 PM, Michael Paquier wrote: > > > > On Mon, Mar 30, 2015 at 6:54 PM, Kavi wrote: > >> >> I sample data as below- >> >> >> { >> "glossary": { >> "title":

Re: [GENERAL] Why does CREATE INDEX CONCURRENTLY need two scans?

2015-03-31 Thread Michael Paquier
On Wed, Apr 1, 2015 at 9:43 AM, Joshua Ma wrote: > Hi all, > > I was curious about why CONCURRENTLY needs two scans to complete - from > the documentation on HOT (access/heap/README.HOT), it looks like the > process is: > > 1) insert pg_index entry, wait for relevant in-progress txns to finish >

Re: [GENERAL] Policy.c program

2015-04-05 Thread Michael Paquier
On Sun, Apr 5, 2015 at 10:16 PM, Ravi Kiran wrote: > But in the version 9.4.0, I cannot find it. > > I want to know whether this program being removed from this version? No, it has not been removed, this file will be available in the first tarball of the 9.5 series, presumably 9.5 beta1. Still, I

Re: [GENERAL] Postgres fails to start

2015-04-06 Thread Michael Paquier
On Tue, Apr 7, 2015 at 9:25 AM, Haiming Zhang wrote: > 2015-04-07 09:01:43 EST LOG: unrecognized win32 error code: 1392 > > [...] In Windows world, 1392 = ERROR_FILE_CORRUPT = the file or directory is corrupted and unreadable: https://msdn.microsoft.com/en-us/library/windows/desktop/ms681385%28v

Re: [GENERAL] Postgres fails to start

2015-04-06 Thread Michael Paquier
On Tue, Apr 7, 2015 at 10:46 AM, Haiming Zhang wrote: > Thank you for replying. The file was there, is that ok to remove the > corrupted file to recover postgres? "base/2008723533/2107262657.2" Be careful here, I would recommend taking a file-level snapshot before going on and do perhaps-stupid

Re: [GENERAL] Collation problem?

2015-04-26 Thread Michael Paquier
On Sun, Apr 26, 2015 at 5:02 PM, Bjørn T Johansen wrote: > And my problem is that I am using Norwegian in some tables and when using > order by the sort order is not correct for the Norwegian letters.. > So my guestion is if it is possible to get the correct sort order without > recreating all my

Re: [GENERAL]

2015-05-15 Thread Michael Paquier
On Fri, May 15, 2015 at 8:54 PM, Mihamina Rakotomandimby wrote: > On 05/15/2015 02:46 PM, Sachin Srivastava wrote: >> How can I fast my daily pg_dump backup. Can I use parallel option(Which is >> introduced in Postgres 9.3) with Postgres 9.1. There is any way I can use >> this is for 9.1 database.

Re: [GENERAL]

2015-05-18 Thread Michael Paquier
On Mon, May 18, 2015 at 7:04 PM, Sachin Srivastava wrote: > Hi Michael, > > So, as per your suggestion I'll update my database from 9.1.2 to 9.1.15. > > Kindly confirm, which year this 9.1.2 was released and when 9.1.15 was > released. > > > And easily I can upgrade this and what are the steps t

Re: [GENERAL] Does PG support bulk operation in embedded C

2015-05-19 Thread Michael Paquier
On Wed, May 20, 2015 at 8:47 AM, Ravi Krishna wrote: > Essentially in one single sql call, we can do > -- Add new rows > -- Update a set of rows where each row is identified by a bookmark > -- Delete a set of rows where each row is identified by a bookmark > -- Fetch a set of rows where each row i

Re: [GENERAL] replacing jsonb field value

2015-05-30 Thread Michael Paquier
On Sat, May 30, 2015 at 11:52 AM, john.tiger wrote: > using 9.4.2 > > suppose we have > create table test (id serial primary key, data jsonb); > insert into test (data) values ({"a":1, "b":2}) > > want to replace "b" with 3 > > okay, we are retrieving entire record > res = select * from test where

Re: [GENERAL] replacing jsonb field value

2015-05-31 Thread Michael Paquier
On Sat, May 30, 2015 at 9:10 PM, Andreas Kretschmer wrote: > Michael Paquier wrote: > >> >> Append the new value to it the existing field, jsonb has as property >> to enforce key uniqueness, and uses the last value scanned for a given >> key. > > can you sh

Re: [GENERAL] JSONB matching element count

2015-05-31 Thread Michael Paquier
On Sun, May 31, 2015 at 11:07 PM, Arup Rakshit wrote: > Hi, > > This says if matched found or not against the input array : > > '{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'c'] > > But how would I determine how many matched ? Like for the above example, I > see only 2 matched found. You could u

Re: [GENERAL] pg_last_xact_replay_timestamp lies

2015-06-14 Thread Michael Paquier
On Mon, Jun 15, 2015 at 8:30 AM, Anton Bushmelev wrote: > Hello, dear guru =) help me to fix situation when no change made on primary > and standby show lag more than 15 minutes: > master : > postgres=# select * from pg_stat_replication > ; > -[ RECORD 1 ]+-- > pid

Re: [GENERAL] pg_last_xact_replay_timestamp lies

2015-06-14 Thread Michael Paquier
differs for 1 megabyte. > ps: sorry for my English > > > On 06/15/2015 02:57 AM, Michael Paquier wrote: >> >> Isn't your mistake the fact that you rely on the assumption that >> replication lag measured in terms of timestamp is a good thing while >>

Re: [GENERAL] RegisterBackgroundWorker does not actually start a bg worker process in 9.4.4

2015-06-15 Thread Michael Paquier
On Tue, Jun 16, 2015 at 3:58 AM, John Lumby wrote: > I am new to bg_workers so this may be my user error, > but when I build and run the contrib/worker_spi > extension,I find that : > > . starting postgres with the extension named in shared_preload_libraries : > its _PG_init is invo

Re: [GENERAL] Test for char errors

2015-06-17 Thread Michael Paquier
On Wed, Jun 17, 2015 at 5:15 PM, 夏高 wrote: > I don't know why the files are not the same but tests all passed. Helps are > appreciated, thanks! Some tests have multiple expected outputs. In the case of char, there is not only char.out, but as well char_1.out and char_2.out. In your case char_1.out

Re: [GENERAL] Test for char errors

2015-06-18 Thread Michael Paquier
On Fri, Jun 19, 2015 at 8:29 AM, 夏高 wrote: > Thanks Michael! Could you tell me which option determines what expected > output is used? Have a look at results_differ() in pg_regress.c ;) The file selected as expected output is the one with less lines of diffs. -- Michael -- Sent via pgsql-gene

Re: [GENERAL] WAL log archival on standby

2015-06-18 Thread Michael Paquier
On Fri, Jun 19, 2015 at 2:38 PM, James Sewell wrote: > Hey All, > > Is it possible to create WAL archive files on standby servers when using > streaming replication? > Yes and no, standbys do not archive WAL in ~9.4, but you could use archive_mode = 'always' with the upcoming 9.5. I know I can

Re: [GENERAL] cascading replication and replication_slots

2015-06-23 Thread Michael Paquier
On Tue, Jun 23, 2015 at 8:18 PM, Leif Gunnar Erlandsen wrote: > Is it possible to use a replication_slot for a downstream-server when > setting up cascading replication on 9.4 Yes. Just be careful that replication slot data is not included in a base backup. -- Michael -- Sent via pgsql-genera

Re: [GENERAL] Turn off streaming replication - leaving Master running

2015-06-28 Thread Michael Paquier
On Mon, Jun 29, 2015 at 2:42 PM, Andy Erskine wrote: > I'd like to turn off the streaming on my postgresdb (9.2) It is currently > setup to stream from master to a single secondary. > > I want to shutdown the secondary and turn it into another master and connect > it to another application for tes

Re: [GENERAL] Turn off streaming replication - leaving Master running

2015-06-28 Thread Michael Paquier
On Mon, Jun 29, 2015 at 3:34 PM, Andy Erskine wrote: > no snapshot available .. i don't mind running basebackup once i've > finished my test. > > So if someone could help with the steps to turn off replication and bring > secondary up as a standalone db that would be great. > How to promote a no

Re: [GENERAL] Turn off streaming replication - leaving Master running

2015-06-29 Thread Michael Paquier
On Tue, Jun 30, 2015 at 1:39 PM, Andy Erskine wrote: > No i don't want a replica. > > I would like to reconfigure my streaming scenario into two standalone db's > i don't want the Master to be effected in anyway and i want it running > consistantly .. the secondary i want to reconfigure as a stan

Re: [GENERAL] Turn off streaming replication - leaving Master running

2015-06-29 Thread Michael Paquier
On Tue, Jun 30, 2015 at 2:29 PM, Andy Erskine wrote: > Ok so a question i should have asked at the very beginning .. > > If i touch my trigger file - promoting the secondary to a master - will > that in anyway effect the master thats already running ? > IE no files on the master will change ? >

Re: [GENERAL] how to extract the page "address" from the ctid

2015-06-30 Thread Michael Paquier
On Tue, Jun 30, 2015 at 10:56 PM, Marc Mamin wrote: > while waiting for the new BRIN and improved GIN Indexes :-) > > I'd like to get a rough feeling, how some column's values are distributed on > the internal pages. > Is there a quicker way to extract the page reference from the ctid? Isn't it

Re: [GENERAL] Confirming - materialized views cannot have foreign key constraints.

2015-07-12 Thread Michael Paquier
On Sun, Jul 12, 2015 at 7:54 AM, David G. Johnston < david.g.johns...@gmail.com> wrote: > Working with 9.3 at the moment and I don't see any syntax that would let > me create one... > I don't recall you can create FKs on materialized views. CREATE MATERIALIZED VIEW has no option regarding that, a

Re: [GENERAL] Where to place suggestions for documentation improvements

2015-07-14 Thread Michael Paquier
On Tue, Jul 14, 2015 at 8:17 PM, Charles Clavadetscher wrote: > I have a generic question. Where should I/we place suggestions on possible > improvements of the documentation? Is it here or better on pgsql-docs? pgsql-docs may be a better place than this list dedicated to general questions, still

Re: [GENERAL] Need help on postgres related to insertion of a tuple.

2015-07-16 Thread Michael Paquier
On Thu, Jul 16, 2015 at 5:18 PM, M Tarkeshwar Rao wrote: > a. How can we prevent "This has been seen to occur with buggy kernels; > consider updating your system" alarm ? > b. how can resolve "This has been seen to occur with buggy kernels; consider > updating your system" alarm ? This defense ha

Re: [GENERAL] Getting error "IST FATAL: the database system is in recovery mode"

2015-07-17 Thread Michael Paquier
On Fri, Jul 17, 2015 at 9:47 PM, Sachin Srivastava wrote: > I am getting the below error in my database pg_log file and also I am unable > to open the database. > "IST FATAL: the database system is in recovery mode" > I am using postgresql version 9.1 on Centos 6.6. Kindly suggest why this > prob

Re: [GENERAL] Recovering database from crashed HD (bad sectors)

2015-07-18 Thread Michael Paquier
On Sat, Jul 18, 2015 at 6:59 PM, Amitabh Kant wrote: > A development box hard disk failed which was running a PG instance with > multiple databases on it. I got the data recovered with some bad sector > errors. Ran another instance of PG (same version), and was to able to take > dump (using pg_du

Re: [GENERAL] Postgres Recovery

2015-07-20 Thread Michael Paquier
On Mon, Jul 20, 2015 at 7:00 PM, Ramesh T wrote: > Hi All, > What i need to know for postgres recovery..?let me know in detail. Documentation is always a good start: http://www.postgresql.org/docs/devel/static/backup.html -- Michael -- Sent via pgsql-general mailing list (pgsql-gene

Re: [GENERAL] Creating a user for pg_start_backup

2015-07-21 Thread Michael Paquier
On Tue, Jul 21, 2015 at 4:47 PM, Andrew Beverley wrote: > Dear all, > > I'm setting up hot backups on my database server. As such, I'd like to set up > a > Postgres user that has access to only pg_start_backup and pg_stop_backup. > > I'm unable to work out how to do this with the various GRANT op

Re: [GENERAL] how to compile postgresql with other version of openssl?

2015-07-26 Thread Michael Paquier
On Sun, Jul 26, 2015 at 2:55 PM, krz...@gmail.com wrote: > Postgresql is first linux program based on automake I've ever seen > (and i've been compiling linux stuff nice 1999) that does not allow > specyfying lib locations on ./configure like > (like --with-openssl=/path/to/openssl) You could use

Re: [GENERAL] how to compile postgresql with other version of openssl?

2015-07-26 Thread Michael Paquier
On Mon, Jul 27, 2015 at 8:34 AM, krz...@gmail.com wrote: > setting LDFLAGS to openssl lib dir causes postgresql ./configure to > fail without real explanation. (Please do not top-post). It may be missing dependencies. You should check config.log for more input about the reason why it failed and t

Re: [GENERAL] xmin horizon?

2015-07-29 Thread Michael Paquier
On Thu, Jul 30, 2015 at 4:13 AM, CS DBA wrote: > The documentation for pg_stat_activity lists this column: > > backend_xmin xid The current backend's xmin horizon. > > Can someone point me to a better understanding on "xmin horizon"? This defines the oldest transaction ID that a given bac

Re: [GENERAL] How to restore

2015-08-09 Thread Michael Paquier
On Mon, Aug 10, 2015 at 2:41 PM, Sachin Srivastava wrote: > Dear Team, > > If I have taken any backup successfully through pg_dump? How can I restore > this pg_dump(Sql file) without use of pg_restore. > There is any command line option or through psql, kindly inform to me. The way to restore a b

Re: [GENERAL] Re: Postgresql 9.3 hotstandby replication error, icorrect checksome in control file

2015-08-21 Thread Michael Paquier
On Fri, Aug 21, 2015 at 7:56 PM, Amit Bondwal wrote: > 2015-08-21 15:35:03 IST [21507-2] DETAIL: The primary's identifier is > 6185381639353264094, the standby's identifier is 6185382953613258663. > > It gives the error "database system identifier differs between the primary > and standby". > T

Re: [GENERAL] [BUGS] error on online backup using pg_basebackup tool

2015-09-02 Thread Michael Paquier
On Thu, Sep 3, 2015 at 10:28 AM, Gerdan Rezende dos Santos wrote: > 6 - create table spaces: > CREATE TABLESPACE newtblspc LOCATION '/data/newtblspc'; > CREATE TABLESPACE newtblspc2 LOCATION '/data/pg_tblspc/newtblspc2' > 7 - mkdir /data2 > 8 - Now perform a backup using pg_basebackup -D /data2 an

Re: [GENERAL] error on online backup using pg_basebackup tool

2015-09-02 Thread Michael Paquier
On Thu, Sep 3, 2015 at 12:20 PM, Gerdan Rezende dos Santos wrote: >[...] Note 1: Please don't top-post. Note 2: Please don't post across multiple mailing lists. > I use the tablespaces without problem but the backup online not complete... > but when I attempt to online backup using the pg_baseba

Re: [GENERAL] modify postgresql.conf

2015-09-06 Thread Michael Paquier
On Mon, Sep 7, 2015 at 12:12 PM, Tom Smith wrote: > Hi, > > Can setting be appended to the end of the postgresql.conf > so that it will override whatever is already in the previous version. > For example > > the existing postgresql.conf already has setting > > max_connections = 100 > > in the midd

Re: [GENERAL] log_statement = 'mod' does not log all data modifying statements

2015-09-09 Thread Michael Paquier
On Wed, Sep 9, 2015 at 11:24 PM, Igor Neyman wrote: > Could you please provide reference to pg_audit? Should be here: http://pgaudit.org/ https://github.com/pgaudit/pgaudit -- Michael -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: ht

Re: [GENERAL] issue, dumping and restoring tables with table inheritance can alter column order

2015-09-28 Thread Michael Paquier
On Tue, Sep 15, 2015 at 7:19 AM, Jim Nasby wrote: > On 9/14/15 11:59 AM, David G. Johnston wrote: >> >> On Mon, Sep 14, 2015 at 11:11 AM, Ingmar Brouns wrote: >> However, when I dump the schema using pg_dump, and then load the >> resulting sql file, suddenly 'c' does follow 'a'. So restorin

Re: [GENERAL] Replication with 9.4

2015-10-03 Thread Michael Paquier
On Sat, Oct 3, 2015 at 8:09 PM, Madovsky wrote: > I would like to fix a issue I'm facing of with the version 9.4 streaming > replication. > is it possible to set on the fly the synchronous commit on the master (or > standby?) > which only sync commit the hot standby node used by the client who has

Re: [GENERAL] Replication with 9.4

2015-10-03 Thread Michael Paquier
On Sat, Oct 3, 2015 at 10:20 PM, Madovsky wrote: > > > On 10/3/2015 4:48 AM, Michael Paquier wrote: >> >> On Sat, Oct 3, 2015 at 8:09 PM, Madovsky wrote: >>> >>> I would like to fix a issue I'm facing of with the version 9.4 streaming >>>

Re: [GENERAL] Replication with 9.4

2015-10-03 Thread Michael Paquier
On Sun, Oct 4, 2015 at 6:38 AM, Madovsky wrote: > On 10/3/2015 6:55 AM, Michael Paquier wrote: >> On Sat, Oct 3, 2015 at 10:20 PM, Madovsky wrote: >> Requesting the master would be necessary, still I don't really get why >> you don't want to query the master fo

Re: [GENERAL] Replication with 9.4

2015-10-04 Thread Michael Paquier
(Seems like you forgot to push the Reply-all button) On Sun, Oct 4, 2015 at 7:01 PM, Madovsky wrote: > On 10/3/2015 3:30 PM, Michael Paquier wrote: >> and no reason is given to justify *why* this would be needed in your case > reason for a choice can be often an issue for other :D &

Re: [GENERAL] Not storing MD5 hashed passwords

2015-10-14 Thread Michael Paquier
On Thu, Oct 15, 2015 at 7:19 AM, Jeff Janes wrote: > On Wed, Oct 14, 2015 at 1:41 PM, John R Pierce wrote: >> >> On 10/14/2015 1:31 PM, Quiroga, Damian wrote: >> >> >> >> Does postgres support other (stronger) hashing algorithms than MD5 to >> store the database passwords at disk? >> >> If not, i

Re: [GENERAL] How to get the session user in a C user defined function

2015-10-14 Thread Michael Paquier
On Thu, Oct 15, 2015 at 2:38 PM, John Leiseboer wrote: > I would like to get the session_user into a C char[] in a C language UDF. I > have found what appears to be a function returning a Datum type called > session_user, but I'm having trouble working out how to call it from within > my C func

Re: [GENERAL] Deadlock detected after pg_repack receives SIGINT

2015-11-03 Thread Michael Paquier
On Tue, Nov 3, 2015 at 9:51 PM, Jiří Hlinka wrote: > After the 10 min timeout, the OS sends SIGINT to pg_repack process so the > pg_repack calls: > SELECT repack.repack_drop($1, $2) > and it causes a deadlock with other process which is INSERTing into > frequently_updated_table that has a pg_repac

Re: [GENERAL] pg_archivecleanup not deleting anything?

2015-11-03 Thread Michael Paquier
On Tue, Nov 3, 2015 at 1:33 AM, Adrian Klaver wrote: > On 11/02/2015 08:17 AM, Paul Jungwirth wrote: >>> >>> Is there anything else beside *.backup files in the directory? >> >> >> There were a few *.history files, and a few files with no extension, >> like this: 000600BE0040. > > > So

Re: [GENERAL] pg_archivecleanup not deleting anything?

2015-11-03 Thread Michael Paquier
On Tue, Nov 3, 2015 at 11:29 PM, Albe Laurenz wrote: > Michael Paquier wrote: >>> So, as Albe posted pg_archivecleanup is only cleaning up the WAL files, not >>> the auxiliary files. The WAL files would be the ones with no extension and a >>> size of 16 MB(unle

Re: [GENERAL] Deadlock detected after pg_repack receives SIGINT

2015-11-04 Thread Michael Paquier
On Wed, Nov 4, 2015 at 10:16 PM, Jiří Hlinka wrote: > I'm on pg_repack 1.3.2 (latest sable, no devel version available to check > if it is already fixed). > > Michael: your memories are fresh and clear :-), yes, it is part of a > cleanup rollback. The problem is, that the pgrepack_drop call this

Re: [GENERAL] pg_archivecleanup not deleting anything?

2015-11-04 Thread Michael Paquier
On Wed, Nov 4, 2015 at 7:16 PM, Albe Laurenz wrote: > Michael Paquier wrote: >>>> The docs mention that "all WAL files" preceding a given point are >>>> removed, personally I understand that as "all 16MB-size segments shall >>>> die", h

Re: [GENERAL] Deadlock detected after pg_repack receives SIGINT

2015-11-05 Thread Michael Paquier
On Fri, Nov 6, 2015 at 4:08 PM, Jiří Hlinka wrote: > my point was, that pg_repack deadlocked itself - I think it should be > possible to guarantee deadlock-free behavior at least via advisory lock for > operations of pg_repack itself (I understand it is not possible to guarantee > this across more

Re: [GENERAL] UUID datatype

2016-05-30 Thread Michael Paquier
On Mon, May 30, 2016 at 4:25 PM, Sridhar N Bamandlapally wrote: > Hi > > Is there a way to implicit SELECT on UUID datatype in uppercase ? You could always cast an UUID back to text and use that with upper(), though you are not explaining what you are tying to achieve: =# select upper(gen_random_

Re: [GENERAL] psql remote shell command

2016-06-02 Thread Michael Paquier
On Fri, Jun 3, 2016 at 8:30 AM, Dennis wrote: > Is it possible to execute command in on system the is hosting postgresql > remotely using psql or other mechanism? I know I can use \! in psql but > that executes the commands on the host where I am running psql from. Also, > is it possible for a p

Re: [GENERAL] psql remote shell command

2016-06-02 Thread Michael Paquier
On Fri, Jun 3, 2016 at 8:48 AM, David G. Johnston wrote: > On Thu, Jun 2, 2016 at 7:39 PM, Michael Paquier > wrote: > I was focused on admin task due to the pg_ctl (not sure you'd want to run > that via psql...) but if you have shell script applications you want to run >

Re: [GENERAL] WAL's listing in pg_xlog by some sql query

2016-06-02 Thread Michael Paquier
On Fri, Jun 3, 2016 at 11:23 AM, Sameer Kumar wrote: > > > On Fri, Jun 3, 2016 at 4:30 AM Stephen Frost wrote: >> >> * Sameer Kumar (sameer.ku...@ashnik.com) wrote: >> > On Fri, 3 Jun 2016, 12:14 a.m. Alex Ignatov, >> > wrote: >> > > Can I list all WAL files in pg_xlog by using some sql query i

Re: [GENERAL] WAL's listing in pg_xlog by some sql query

2016-06-05 Thread Michael Paquier
On Sat, Jun 4, 2016 at 11:34 PM, Vik Fearing wrote: > On 03/06/16 04:32, Michael Paquier wrote: >> pg_ls_dir() has a check on superuser() embedded in its code. > > So what? That's what SECURITY DEFINER is all about. Yes you are right. I missed completely the point :) Thanks

Re: [GENERAL] WAL's listing in pg_xlog by some sql query

2016-06-07 Thread Michael Paquier
On Mon, Jun 6, 2016 at 9:51 PM, Stephen Frost wrote: > * Vik Fearing (v...@2ndquadrant.fr) wrote: >> On 03/06/16 04:32, Michael Paquier wrote: >> > On Fri, Jun 3, 2016 at 11:23 AM, Sameer Kumar >> > wrote: >> >> On Fri, Jun 3, 2016 at 4:30 AM Stephen Frost

Re: [GENERAL] Unregistered OpenSSL callbacks access violation

2016-06-08 Thread Michael Paquier
On Thu, Jun 9, 2016 at 4:01 AM, Ranier VF wrote: > Postgresql 9.5 > WARNING: Stack unwind information not available. Following frames may be > wrong. > SSLEAY32!SSL_shutdown+0x4 > LIBPQ!PQpingParams+0x1631 > LIBPQ!PQfinish+0x11 > nginx!dbd_pgsql_close+0x106 [c:\usr\src\dbd\postgresql\dbd_pgsql.c

Re: [GENERAL] Can SET_VARSIZE cause a memory leak?

2016-06-08 Thread Michael Paquier
On Wed, Jun 8, 2016 at 9:55 PM, Albe Laurenz wrote: > This is safe, and the memory will be freed at the end of the transaction. Yes, palloc takes memory from the current memory context. The case where you would need to use pfree is if there is an allocation continuously done within the same code

Re: [GENERAL] Retrieving comment of rules and triggers

2016-06-09 Thread Michael Paquier
On Fri, Jun 10, 2016 at 6:06 AM, Melvin Davidson wrote: > as the developers have a nasty habit of changing pg_catalog > tables/columns. ... When necessary to improve the quality of the project and the user experience. -- Michael -- Sent via pgsql-general mailing list (pgsql-general@postgresql

Re: [GENERAL] PgQ and pg_dump

2016-06-15 Thread Michael Paquier
Martin wrote: > I wonder if this is the desirable way of handling pgq, or if those > tables should be dumped. I'm starting to think that this is a PgQ bug, > or maybe it's not a good idea to install PgQ as an extension. As I am looking at that I would qualify that as a bug in pg_dump. Schemas can

Re: [GENERAL] PgQ and pg_dump

2016-06-15 Thread Michael Paquier
On Wed, Jun 15, 2016 at 7:19 PM, Martín Marqués wrote: > Hi Michael, > > 2016-06-15 5:00 GMT-03:00 Michael Paquier : >> Martin wrote: >>> I wonder if this is the desirable way of handling pgq, or if those >>> tables should be dumped. I'm starting to think tha

Re: [GENERAL] PgQ and pg_dump

2016-06-16 Thread Michael Paquier
On Thu, Jun 16, 2016 at 8:37 PM, Martín Marqués wrote: > El 16/06/16 a las 00:08, Michael Paquier escribió: >> On Wed, Jun 15, 2016 at 7:19 PM, Martín Marqués >> wrote: >>> >>> How would the recovery process work? We expect the schema to be there >>>

Re: [GENERAL] Pg_bulkload for PostgreSql 9.5

2016-06-17 Thread Michael Paquier
On Fri, Jun 17, 2016 at 8:29 PM, Job wrote: > i have some problems about compiling pg_bulkload-3.1.8 on a CentOS 5 with > Postgresql 9.5. > If i use a previous version of Psql it compile and works. The project page states that 9.5 is supported with this version: https://github.com/ossc-db/pg_bul

Re: [GENERAL] Regression tests (Background Workers)

2016-06-21 Thread Michael Paquier
On Tue, Jun 21, 2016 at 2:02 PM, Dharini wrote: > Background process is initialized at server start and when trying to run the > tests i get the following error. > > $ make installcheck > (using postmaster on Unix socket, default port) > == dropping database "contrib_regression" ==

Re: [GENERAL] ERROR: missing chunk number 0 for toast value while using logical decoder.\

2016-06-23 Thread Michael Paquier
On Thu, Jun 23, 2016 at 3:38 PM, sudalai wrote: > > I am using PostgresSQL 9.5.0. > I have customized : > https://github.com/michaelpq/pg_plugins/tree/master/decoder_raw to get > result as json. It works fine. suddenly i getting below exception while > consuming or peeking changes through that log

Re: [GENERAL] Re: ERROR: missing chunk number 0 for toast value while using logical decoder.\

2016-06-23 Thread Michael Paquier
On Thu, Jun 23, 2016 at 4:55 PM, sudalai wrote: > Thanks for : https://github.com/michaelpq/pg_plugins/tree/master/decoder_raw > I will upgrade my servers to PostgreSQL 9.5.3, but i want to find the > problem. > Please help me. > I don't have SQL sequence. > Does anything i can get from server, th

Re: [GENERAL] pg_archivecleanup - Increase time files are deleted

2016-06-26 Thread Michael Paquier
On Mon, Jun 27, 2016 at 6:42 AM, Melvin Davidson wrote: > >That means there will be at least 256 files before postgres delete them? > > Yes! Not exactly. To be more precise, for an instance generating WAL, the decision regarding the number of WAL segments to remove or recycled is done at the crea

Re: [GENERAL] reject

2016-06-26 Thread Michael Paquier
On Mon, Jun 27, 2016 at 9:21 AM, 유성열 wrote: > Please, I want to unsubscribe your email. Delete my mail address in your > mail list, please...OMG.. > You can do it by yourself here: https://www.postgresql.org/community/lists/subscribe/ There is an unsubscribe option. -- Michael

Re: [GENERAL] How safe is pg_basebackup + continuous archiving?

2016-06-29 Thread Michael Paquier
On Wed, Jun 29, 2016 at 11:51 PM, Kaixi Luo wrote: > We use PostgreSQL at work and we do daily backups with pg_dump. After that > we pg_restore the dump and check the database that there isn't any data > corruption. As the database grows, the whole pg_dump / pg_restore cycle time > is quickly appr

Re: [GENERAL] How safe is pg_basebackup + continuous archiving?

2016-06-30 Thread Michael Paquier
On Thu, Jun 30, 2016 at 9:00 PM, Kaixi Luo wrote: >> Before replaying a backup on a production system, you would need a >> pre-production setup where the backup is replayed and checked. >> Honestly, you can only be sure that a backup is working correctly >> after reusing it. You could always do so

Re: [GENERAL] How sync settings or extensions in streaming replication

2016-07-05 Thread Michael Paquier
On Wed, Jul 6, 2016 at 11:37 AM, 苏士涛 wrote: > I am new to PostgreSQL, and i have a question about streaming > replication. If i enable some extension on master with or without > native code(in c), do I need to enable this extension on slave? Or > PostgreSQL sync this automatic. Same to settings in

Re: [GENERAL] pg_basebackup vs archive_command - looking for WAL archive older than archive_command start

2016-07-17 Thread Michael Paquier
On Mon, Jul 18, 2016 at 7:35 AM, Francisco Reyes wrote: > I turned on archive_command and have wal archiving going. > > I did a pg_basebackup and copied the resulting file from source machine to > target, yet when I restore I am getting > > requested WAL segment 000508AE009B has alread

Re: [GENERAL] Re: ERROR: missing chunk number 0 for toast value while using logical decoder.\

2016-07-18 Thread Michael Paquier
On Mon, Jul 18, 2016 at 4:31 PM, sudalai wrote: > Please help me, I am using PostgreSQL 9.5.0. > I don't know much in postgres internals. why does peek_changes trying > to take share lock on 248486 (primary key of one table). > Its postgres logical decoder takes share lock or outpu

Re: [GENERAL] Re: ERROR: missing chunk number 0 for toast value while using logical decoder.\

2016-07-18 Thread Michael Paquier
On Mon, Jul 18, 2016 at 7:08 PM, sudalai wrote: > thank u. > why (select * from pg_logical_slot_peek_changes('slot',NULL,1) ) take share > lock ? > any idea ? My guess is that this lock comes from your plugin code. Please double-check it. -- Michael -- Sent via pgsql-general mailing list (p

Re: [GENERAL] pg_dumping extensions having sequences with 9.6beta3

2016-07-22 Thread Michael Paquier
On Fri, Jul 22, 2016 at 6:27 PM, Philippe BEAUDOIN wrote: > I am currently playing with extensions. And I found a strange behaviour > change with 9.6beta2 and 3 when pg_dumping a database with an extension > having sequences. This looks like a bug, ... unless I did something wrong. > [...] > => as

Re: [GENERAL] pg_dumping extensions having sequences with 9.6beta3

2016-07-26 Thread Michael Paquier
On Tue, Jul 26, 2016 at 4:50 PM, Noah Misch wrote: > [Action required within 72 hours. This is a generic notification.] > > The above-described topic is currently a PostgreSQL 9.6 open item. Stephen, > since you committed the patch believed to have created it, you own this open > item. If some

Re: [GENERAL] pg_dumping extensions having sequences with 9.6beta3

2016-07-26 Thread Michael Paquier
On Wed, Jul 27, 2016 at 8:07 AM, Stephen Frost wrote: > That'd be great. It's definitely on my list of things to look into, but > I'm extremely busy this week. I hope to look into it on Friday, would > be great to see what you find. Sequences that are directly defined in extensions do not get d

Re: [GENERAL] low perfomances migrating from 9.3 to 9.5

2016-07-27 Thread Michael Paquier
On Wed, Jul 27, 2016 at 5:54 PM, thomas veymont wrote: > We switched from PostgreSQL server 9.3 to 9.5. From the very beginning there > was a noticeable drop in performances (for example : when injecting our SQL > dumps into 9.5, COPY and CREATE INDEX were very slow). > > Our configuration file wa

Re: [GENERAL] low perfomances migrating from 9.3 to 9.5

2016-07-27 Thread Michael Paquier
On Wed, Jul 27, 2016 at 6:22 PM, thomas veymont wrote: > as far as I remember these settings were for faster bulk loading. > > in 9.5 we set: > max_wal_size = 6GB # > https://www.postgresql.org/docs/9.5/static/release-9-5.html, max_wal_size = > (3 * checkpoint_segments) * 16MB

Re: [SPAM] Re: [SPAM] Re: [GENERAL] WAL directory size calculation

2016-08-03 Thread Michael Paquier
On Wed, Aug 3, 2016 at 8:07 PM, Moreno Andreo wrote: > Should I keep fsync off? I'd think it would be better leaving it on, right? >From the docs: >https://www.postgresql.org/docs/9.6/static/runtime-config-wal.html#RUNTIME-CONFIG-WAL-SETTINGS While turning off fsync is often a performance benefi

Re: [GENERAL] fixing failed master after standby promotion

2016-08-07 Thread Michael Paquier
On Mon, Aug 8, 2016 at 11:35 AM, Masahiko Sawada wrote: > On Sun, Aug 7, 2016 at 10:55 PM, Aviel Buskila wrote: >> Hey , >> I have been setting a configuration of 2 nodes (master and standby) >> replicating using repmgr, and 1 server using for load balancing using >> pgpool-II. >> Now after the m

Re: [GENERAL] Extract data from JSONB

2016-08-07 Thread Michael Paquier
On Mon, Aug 8, 2016 at 12:08 PM, Alex Magnum wrote: > How can I convert that into one row each based on status; for example if I > only want to have the active modules. You can use jsonb_each to decompose that: =# select key, (value::json)->'status' from jsonb_each('{

Re: [GENERAL] pgbasebackup is failing after truncate

2016-08-15 Thread Michael Paquier
On Sat, Aug 13, 2016 at 12:41 AM, Jeff Janes wrote: > On Wed, Aug 10, 2016 at 11:06 PM, Yelai, Ramkumar > wrote: >> At present, I have some requirement to truncate the data base to reclaim >> disk space and at the same time I need to take basebackup. >> >> Seems, truncate is successfully reclaim

Re: [GENERAL] PostgreXL

2016-08-16 Thread Michael Paquier
On Tue, Aug 16, 2016 at 8:30 PM, Trupti Padiya wrote: > I am new to postgreXL and I want to perform the following: > > 1) I have set of tables which i want to put on server/coordinator > 2) Out of those tables, I want to put few tables on node 1, and few on node > 2 and few on both nodes. > > Say

Re: [GENERAL] incorrect checksum detected on "global/pg_filenode.map" when VACUUM FULL is executed

2016-08-21 Thread Michael Paquier
On Mon, Aug 22, 2016 at 12:27 PM, Tatsuki Kadomoto wrote: > I see incorrect checksum detected on "global/pg_filenode.map" when "VACUUM > FULL" is executed. > > The error message didn't repeat. It showed up only once. > > Is this expected? Can someone give me a plausible scenario why this > happene

Re: [GENERAL] incorrect checksum detected on "global/pg_filenode.map" when VACUUM FULL is executed

2016-08-21 Thread Michael Paquier
On Mon, Aug 22, 2016 at 1:31 PM, John R Pierce wrote: > On 8/21/2016 9:13 PM, Tatsuki Kadomoto wrote: >> >> Can we point out a specific bug that can lead to this? > > > 9.2.6 fixed several data corruption bugs, > https://www.postgresql.org/docs/current/static/release-9-2-6.html > > 9.2.9 fixed a G

Re: [GENERAL] incorrect checksum detected on "global/pg_filenode.map" when VACUUM FULL is executed

2016-08-22 Thread Michael Paquier
On Mon, Aug 22, 2016 at 4:45 PM, Tatsuki Kadomoto wrote: > Thanks for suggestion for upgrade. I know that's the way to go, but it's not > so easy due to circumstances on my side. Well, I guess it depends on how much you care about your data. > Meanwhile, could you tell me what is this "global/pg

Re: [GENERAL] incorrect checksum detected on "global/pg_filenode.map" when VACUUM FULL is executed

2016-08-25 Thread Michael Paquier
On Thu, Aug 25, 2016 at 9:48 PM, Tatsuki Kadomoto wrote: > Does this mean it's a kind of expected to face checksum error when the > mapping file is relocated by VACUUM FULL? > I faced the checksum error exactly when VACUUM FULL was running and it has > never been detected until now. No, it's no

Re: [GENERAL] incorrect checksum detected on "global/pg_filenode.map" when VACUUM FULL is executed

2016-08-25 Thread Michael Paquier
On Fri, Aug 26, 2016 at 9:09 AM, Tatsuki Kadomoto wrote: > Does Postgre have any mechanism to correct the mapping file automatically? When the relation mapping changes, the file gets rewritten at transaction commit. I guess that the error you saw previously (be careful, that's a corruption!) got

Re: [GENERAL] UPDATE OR REPLACE?

2016-08-31 Thread Michael Paquier
On Thu, Sep 1, 2016 at 12:10 PM, dandl wrote: > Sqlite has options to handle an update that causes a duplicate key. Is there > anything similar in Postgres? > This is not an UPSERT. The scenario is an UPDATE that changes some key field > so that there is now a duplicate key. In Sqlite this handled

  1   2   3   4   5   6   >