Re: [GENERAL] postgresql 9.4 streaming replication

Do you have a client with a propper recovery.conf running? Am 19. Oktober 2015 09:58:40 MESZ, schrieb "Sven Löschner" : >I have the following problem: i am trying to set up a streaming >replication scenario with load balancing. I read various tutorials but > >i cannot find the

[GENERAL] postgresql 9.4 streaming replication

I have the following problem: i am trying to set up a streaming replication scenario with load balancing. I read various tutorials but i cannot find the mistake. The replication does not work. I do not have a "wal sender/receiver process". The archiving works and everytime the master

Re: [GENERAL] PSQL Tools

On 2015-10-18 16:37, Nicolas Paris wrote: > > ​I didn't know DBeaver, it looks great ! (I personnally have many bugs/freeze > with pgadmin3 on ubuntu, moreover the interface is not userfriendly)​ It also has the advantage of working with all the major database engines, so you can use the same

Re: [GENERAL] Recommendations for migrating PG 9.3 RDS across regions

On 10/17/15 5:24 PM, Joshua Ma wrote: Is one of Londiste, Slony, or Bucardo obviously better than the others for this task? At first glance Bucardo seems the most straightforward, but that may just be due to the docs being easier to glance through. (I've also never used any postgres replication

Re: [GENERAL] ERROR: tablespace "archive2" is not empty

On 2015-10-19 11:14:33 +0200, Josip Rodin wrote: > On Mon, Oct 19, 2015 at 11:06:59AM +0200, Andres Freund wrote: > > Hi, > > > > On 2015-10-19 10:49:11 +0200, Josip Rodin wrote: > > > % sudo -H -u postgres psql mydb -c "SELECT oid, relname, relkind FROM > > > pg_catalog.pg_class where oid IN

Re: [GENERAL] [BUGS] postgresql table data control

On Sat, Oct 17, 2015 at 1:26 AM, 許耀彰 wrote: > Dear Support Team, > How can we know each table data increase day by day? It mean how do we get > how many data produce today,included which data? Thank you. > [moving from bugs@] Please refer to these SQL-level functions:

Re: [GENERAL] postgresql 9.4 streaming replication

My client recovery.conf looks this way: standby_mode = 'on' primary_conninfo = 'host=arcserver1 port=5432 user=postgres pass=postgres' restore_command = 'pg_standby /db/pg_archived %f %p >> /var/log/standby.log' primary_slot_name='standby1' Zitat von Andreas Kretschmer

Re: [GENERAL] postgresql 9.4 streaming replication

On 2015-10-19 08:34:51 +, Sven Löschner wrote: > My client recovery.conf looks this way: > > standby_mode = 'on' > primary_conninfo = 'host=arcserver1 port=5432 user=postgres pass=postgres' > restore_command = 'pg_standby /db/pg_archived %f %p >> /var/log/standby.log' >

Re: [GENERAL] ERROR: tablespace "archive2" is not empty

On Mon, Oct 19, 2015 at 11:06:59AM +0200, Andres Freund wrote: > Hi, > > On 2015-10-19 10:49:11 +0200, Josip Rodin wrote: > > % sudo -H -u postgres psql mydb -c "SELECT oid, relname, relkind FROM > > pg_catalog.pg_class where oid IN (7877054, 7877056);" > > oid | relname | relkind > >

Re: [GENERAL] PSQL Tools

On Sun, 2015-10-18 at 14:48 +, jwienc...@comcast.net wrote: > > Hello > > I am looking for a tool like TOAD for DBA's.   I would like something > that can generate/extract DDL; report manage tablespace, create > explain plans, create/mange users, synonyms, triggers, etc. > > > regards > >

Re: [GENERAL] ERROR: tablespace "archive2" is not empty

On Mon, Oct 19, 2015 at 11:42:38AM +0200, Andres Freund wrote: > On 2015-10-19 11:14:33 +0200, Josip Rodin wrote: > > On Mon, Oct 19, 2015 at 11:06:59AM +0200, Andres Freund wrote: > > > Hi, > > > > > > On 2015-10-19 10:49:11 +0200, Josip Rodin wrote: > > > > % sudo -H -u postgres psql mydb -c

Re: [GENERAL] postgresql 9.4 streaming replication

I inserted the following in my pg_hba.conf to test, but it does not work: hostreplication rep_user0.0.0.0/0 trust hostall postgres0.0.0.0/0 trust thank you in advance, Sven WAL sender process starts when streaming client

[GENERAL] ERROR: tablespace "archive2" is not empty

Hi, I've run into an odd problem - I have what seems to be a "zombie" tablespace that PostgreSQL won't let me drop, but nothing inside it is active. % sudo -H -u postgres psql template1 -c "drop tablespace archive2;" ERROR: tablespace "archive2" is not empty % sudo find /media/ssd/archive2/

Re: [GENERAL] Version management for extensions

On 10/18/15 6:43 PM, Jeff Janes wrote: It seems like there should be some way to mark a feature-release of an extension, versus a server-compatibility-only release (also versus a bug-fix-in-extension release). The problem is worse than that: the 'version' string for an extension is just

Re: [GENERAL] ID column naming convention

On 10/18/15 4:32 AM, Karsten Hilbert wrote: Can you tell us more about the database (ignoring nit-picking!)? Sure. All the clinical tables inherit from clin.clin_root_item which itself has a primary key (pk_item) and which, in turn, inherits from audit.audit_fields, which, again, has a primary

Re: [GENERAL] ERROR: tablespace "archive2" is not empty

On Mon, Oct 19, 2015 at 04:39:55AM -0500, Jim Nasby wrote: > On 10/19/15 4:14 AM, Josip Rodin wrote: > >On Mon, Oct 19, 2015 at 11:06:59AM +0200, Andres Freund wrote: > >>Hi, > >> > >>On 2015-10-19 10:49:11 +0200, Josip Rodin wrote: > >>>% sudo -H -u postgres psql mydb -c "SELECT oid, relname,

Re: [GENERAL] ERROR: tablespace "archive2" is not empty

On 10/19/15 4:14 AM, Josip Rodin wrote: On Mon, Oct 19, 2015 at 11:06:59AM +0200, Andres Freund wrote: Hi, On 2015-10-19 10:49:11 +0200, Josip Rodin wrote: % sudo -H -u postgres psql mydb -c "SELECT oid, relname, relkind FROM pg_catalog.pg_class where oid IN (7877054, 7877056);" oid |

Re: [GENERAL] postgresql 9.4 streaming replication

On 19 October 2015 at 09:41, Sven Löschner wrote: > I inserted the following in my pg_hba.conf to test, but it does not work: > > hostreplication rep_user0.0.0.0/0 trust > hostall postgres0.0.0.0/0 trust > >

Re: [GENERAL] ERROR: tablespace "archive2" is not empty

Hi, On 2015-10-19 10:49:11 +0200, Josip Rodin wrote: > % sudo -H -u postgres psql mydb -c "SELECT oid, relname, relkind FROM > pg_catalog.pg_class where oid IN (7877054, 7877056);" > oid | relname | relkind > -+-+- > (0 rows) That's the wrong query. The files on disk are

Re: [GENERAL] postgresql 9.4 streaming replication

WAL sender process starts when streaming client is active. The connection is initiated by the client. That's why you cannot see it. It also uses the TCP-port that is used by any other pg client. There is a special entry in the pg_hba.conf for the replication clients. Refer to this please:

Re: [GENERAL] Can we make regexp processing more friendly by recognizing "\r\n" as a "newline" for "^$" purposes?

On Mon, Oct 19, 2015 at 1:26 AM, Francisco Olarte wrote: > Hi David: > > On Sun, Oct 18, 2015 at 7:49 PM, David G. Johnston > wrote: > > Other implementation of regular expressions handle "newline" mechanics > > related to "^" and "$"

Re: [GENERAL] ERROR: tablespace "archive2" is not empty

On 10/19/2015 03:18 AM, Josip Rodin wrote: On Mon, Oct 19, 2015 at 11:42:38AM +0200, Andres Freund wrote: On 2015-10-19 11:14:33 +0200, Josip Rodin wrote: On Mon, Oct 19, 2015 at 11:06:59AM +0200, Andres Freund wrote: Hi, On 2015-10-19 10:49:11 +0200, Josip Rodin wrote: % sudo -H -u

Re: [GENERAL] ERROR: tablespace "archive2" is not empty

On Mon, Oct 19, 2015 at 07:38:32AM -0700, Adrian Klaver wrote: > What happens if you do?: > > select oid, * from pg_tablespace ; mydb=> select oid, * from pg_tablespace where spcname = 'archive2'; oid | spcname | spcowner | spclocation | spcacl | spcoptions

Re: [GENERAL] ERROR: tablespace "archive2" is not empty

On 10/19/2015 07:56 AM, Josip Rodin wrote: On Mon, Oct 19, 2015 at 07:38:32AM -0700, Adrian Klaver wrote: What happens if you do?: select oid, * from pg_tablespace ; mydb=> select oid, * from pg_tablespace where spcname = 'archive2'; oid | spcname | spcowner | spclocation |

Re: [GENERAL] ERROR: tablespace "archive2" is not empty

On Mon, Oct 19, 2015 at 08:23:24AM -0700, Adrian Klaver wrote: > On 10/19/2015 07:56 AM, Josip Rodin wrote: > >On Mon, Oct 19, 2015 at 07:38:32AM -0700, Adrian Klaver wrote: > >>What happens if you do?: > >> > >>select oid, * from pg_tablespace ; > > > >mydb=> select oid, * from pg_tablespace

Re: [GENERAL] ERROR: tablespace "archive2" is not empty

On 10/19/2015 07:56 AM, Josip Rodin wrote: On Mon, Oct 19, 2015 at 07:38:32AM -0700, Adrian Klaver wrote: What happens if you do?: select oid, * from pg_tablespace ; mydb=> select oid, * from pg_tablespace where spcname = 'archive2'; oid | spcname | spcowner | spclocation |

Re: [GENERAL] ERROR: tablespace "archive2" is not empty

Josip Rodin writes: > On Mon, Oct 19, 2015 at 11:42:38AM +0200, Andres Freund wrote: That's the wrong query. The files on disk are relefilenodes not oids. Try WHERE pg_relation_filenode(oid) IN ... > Oh, sorry, but yet again, there's just nothing there: > %

Re: [GENERAL] ERROR: tablespace "archive2" is not empty

On 10/19/2015 08:28 AM, Josip Rodin wrote: On Mon, Oct 19, 2015 at 08:23:24AM -0700, Adrian Klaver wrote: On 10/19/2015 07:56 AM, Josip Rodin wrote: On Mon, Oct 19, 2015 at 07:38:32AM -0700, Adrian Klaver wrote: What happens if you do?: select oid, * from pg_tablespace ; mydb=> select oid,

Re: [GENERAL] ERROR: tablespace "archive2" is not empty

Josip Rodin writes: > Ah, I forgot to mention. 7849107 is symlinked to /media/archive2/postgresql, > so the symlink is referencing a real directory, and spclocation is broken > because that doesn't exist. But that sounds like an -ENOENT and not > -ENOPERM, no?

Re: [GENERAL] ERROR: tablespace "archive2" is not empty

On Mon, Oct 19, 2015 at 08:24:10AM -0700, Tom Lane wrote: > Josip Rodin writes: > > On Mon, Oct 19, 2015 at 11:42:38AM +0200, Andres Freund wrote: > That's the wrong query. The files on disk are relefilenodes not > oids. Try WHERE pg_relation_filenode(oid) IN

[GENERAL] Are there any major benefits to using point and not real data type

I am using postgres 9.4 and am new to this but I have 2 columns right now latitudes & longitudes. I have them saved as a real column so they take up 4 bits each 8 altogether. I see that point is used for latitudes and longitudes but it is a whopping 16 bits are there any benefits (Performance) of

Re: [GENERAL] ERROR: tablespace "archive2" is not empty

On Mon, Oct 19, 2015 at 11:15:08AM -0700, Adrian Klaver wrote: > >Sorry, that's another typo. It's the latter. The symlink is indeed pointing > >to the 5 GB of leftovers. > > This: > > % sudo find /media/ssd/archive2/ -type f -ls > 36962439 393940 -rw--- 1 postgres postgres 403390464 Jun

Re: [GENERAL] ERROR: tablespace "archive2" is not empty

On 19. listopada 2015. 17:46:19 CEST, Adrian Klaver wrote: >On 10/19/2015 08:28 AM, Josip Rodin wrote: >> On Mon, Oct 19, 2015 at 08:23:24AM -0700, Adrian Klaver wrote: >>> On 10/19/2015 07:56 AM, Josip Rodin wrote: On Mon, Oct 19, 2015 at 07:38:32AM -0700,

Re: [GENERAL] Are there any major benefits to using point and not real data type

On 10/19/2015 11:33 AM, romeo407 wrote: I am using postgres 9.4 and am new to this but I have 2 columns right now latitudes & longitudes. I have them saved as a real column so they take up 4 bits each 8 altogether. I see that point is used for latitudes and longitudes but it is a whopping 16

Re: [GENERAL] interperting type oid in C code

SELECT typname FROM pg_type WHERE oid = Oid_x; On Mon, Oct 19, 2015 at 2:36 PM, Ken Been wrote: > I'm working on a foreign data wrapper and I want to switch based on the > column type. Specifically, if the column type in the external table is the > same as in the (locally

Re: [GENERAL] interperting type oid in C code

Those are more complicated, and it's not obvious to me how to use them. I really think that all I need is something as simple as "if (my_oid == INT4OID) {...}". Is there any reason why I shouldn't just do that? On Mon, Oct 19, 2015 at 7:08 PM, Alvaro Herrera wrote: >

Re: [GENERAL] interperting type oid in C code

Well, it's a few types, but the logic is different for each one, so they have to be handled independently anyway. On Mon, Oct 19, 2015 at 7:40 PM, Alvaro Herrera wrote: > Ken Been wrote: > > Those are more complicated, and it's not obvious to me how to use them. > I >

Re: [GENERAL] ERROR: tablespace "archive2" is not empty

On 10/19/2015 10:26 AM, Josip Rodin wrote: On 19. listopada 2015. 17:46:19 CEST, Adrian Klaver wrote: On 10/19/2015 08:28 AM, Josip Rodin wrote: On Mon, Oct 19, 2015 at 08:23:24AM -0700, Adrian Klaver wrote: On 10/19/2015 07:56 AM, Josip Rodin wrote: On Mon,

[GENERAL] interperting type oid in C code

I'm working on a foreign data wrapper and I want to switch based on the column type. Specifically, if the column type in the external table is the same as in the (locally defined) foreign table then I can get some speedup for some types. Through the ForeignScanState object I can get TupleDesc

Re: [GENERAL] interperting type oid in C code

Ken Been wrote: > Those are more complicated, and it's not obvious to me how to use them. I > really think that all I need is something as simple as "if (my_oid == > INT4OID) {...}". Is there any reason why I shouldn't just do that? I don't know. I was thinking that you might want to handle a

Re: [GENERAL] ERROR: tablespace "archive2" is not empty

On 10/19/2015 11:25 AM, Josip Rodin wrote: On Mon, Oct 19, 2015 at 11:15:08AM -0700, Adrian Klaver wrote: Sorry, that's another typo. It's the latter. The symlink is indeed pointing to the 5 GB of leftovers. This: % sudo find /media/ssd/archive2/ -type f -ls 36962439 393940 -rw--- 1

Re: [GENERAL] interperting type oid in C code

Thanks, but I actually wanted to do it from C code. But anyway I think I found the answer: use the symbolic constants in catalog/pg_type.h, such as INT4OID. On Mon, Oct 19, 2015 at 6:44 PM, Melvin Davidson wrote: > SELECT typname >FROM pg_type > WHERE oid = Oid_x; >

Re: [GENERAL] interperting type oid in C code

Ken Been wrote: > Thanks, but I actually wanted to do it from C code. But anyway I think I > found the answer: use the symbolic constants in catalog/pg_type.h, such as > INT4OID. You can probably use SearchSysCache1(TYPEOID, ObjectIdGetDatum(your_oid)) or perhaps lookup_type_cache(your_oid). --

Re: [GENERAL] [BUGS] postgresql table data control

> On Oct 19, 2015, at 04:29, Shulgin, Oleksandr > wrote: > >> On Sat, Oct 17, 2015 at 1:26 AM, 許耀彰 wrote: >> Dear Support Team, >> How can we know each table data increase day by day? It mean how do we get >> how many data produce

[GENERAL] Returning JSON or JSONB

Hello, I was wondering when returning JSON data from a PostgreSQL function for consumption by clients (e.g., PHP or Lua) does it make any difference to declare the function's return type as JSON or JSONB? Now that I've actually written the question down it occurs to me that what I really want to