Picon

dblink password required

Hi,

I’m trying to use dblink to connect from database1 to database2 on the same machine. The Postgres version
is 9.3.5.

Firstly, I’ve created a user to use with the connection, while logged in as the postgres superuser to database2:

> create user db_link with password ‘mypassword’;
> grant select on appuser to db_link;

Secondly, the following has been added to pg_hba.conf and a restart done.

# dblink between database1 and database2
local database2   db_link                               md5
host database2    db_link       127.0.0.1/32            md5
host database2    db_link       ::1/128                 md5

Connecting with PSQL prompts for a password as expected:

> psql -U db_link -d database2
Password for user db_link:
psql (9.3.5)
Type "help" for help.

Then, as a normal user logged into database1, I tried

> select * from dblink(‘dbname=database2 username=db_link password=mypassword','select
username, email from appuser') as t1(username text, email text);

However this returns
(Continue reading)

TigerNassau | 24 Oct 16:14 2014
Picon

Osx stopping to install

Trying to remove enterprisedb version and install menubar 9.3.5.2. - dragged old app to trash, unzipped
new, dragged to app folder and menubar.  Won't run since server still running.
So did ps aux | grep postgres then sudo kill -3 all the pids

Cannot kill work process - only process remaining

Now postgres will not open - says running

any ideas how to stop?

Sent from my LG Mobile
--

-- 
Sent via pgsql-general mailing list (pgsql-general <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
nurul [via PostgreSQL] | 21 Oct 09:02 2014

Log-shipping replication in one machine

We have install PostgreSQL 9.3 in Ubuntu. We want to ask it is posibble to do log shipping replication in one machine with different port such as port 5435 as a master while 5436 as a slave? We also tried that process in one machine but still get an error in slave such as

warning: connection to the database failed, disabling startup checks:
psql: FATAL: the database system is starting up

We do the log shipping replication process based on http://www.themagicnumber.es/replication-in-postgresql-i?lang=en

We hope all of you can help us to solve this problem. Thank you

If you reply to this email, your message will be added to the discussion below:
http://postgresql.1045698.n5.nabble.com/Log-shipping-replication-in-one-machine-tp5823774.html
This email was sent by nurul (via Nabble)
To receive all replies by email, subscribe to this discussion
Picon

ARMv5

Hello, I have a problem with PostgreSQL. I need to install PostgreSQL on ARMv5 with tcp/ip access, but I have no experience in it. It seems, that first I must do cross-compilation for ARMv5. Can you help me, or advise something? Maybe you have any guides how to install PostgreSQL on ARM?

With best regards,
Alexandr Glukhov
Joe Van Dyk | 24 Oct 04:10 2014

Finding date intersections

I have a table of sales that have possibly overlapping time ranges. I want to find all the timeranges where there's an active sale. How would you do that?

create table sales (
  times tstzrange
);

insert into sales values
  (tstzrange('2014-1-1', '2014-1-2')),
  (tstzrange('2014-1-2', '2014-1-3')),
  (tstzrange('2014-1-2', '2014-1-4')),
  (tstzrange('2014-1-5', '2014-1-6'));

-- want back:
--   tstzrange('2014-1-1', '2014-1-4')
--   tstzrange('2014-1-6', '2014-1-6')

Thanks,
Joe
John Smith | 24 Oct 00:30 2014
Picon

is there a warm standby sync trigger?

i want to setup a warm standby that listens 24/7 but only syncs when
told to (ie only when i am ok with the database updates, will i
trigger the sync).

can i?

i don't want to manually backup and restore like i do now.

thks, jzs

--

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

Joe Van Dyk | 23 Oct 22:40 2014

some queries on standby preventing replication updates

Hi,

I have a master and a slave database. 

I've got hot_standby_feedback turned on, max_standby_streaming_delay=-1. I've configured the master and slave to keep a few days of WALs around.

I've noticed that when some large queries are run on the standby machine (ones that take more than a minute or so), replication updates are paused. Is there a way to fix this?

Thanks,
Joe
twoflower | 23 Oct 16:03 2014
Picon

Emulating flexible regex replace

Hello,

my scenario is this: I have a *SEGMENT* table with two text fields, *source*
and *target*. From the user, I get the following input:

/source pattern/
/target pattern/

Where both patterns are regexes and moreover the target pattern contains
references to the source in the following way: 

Supposing *source* matches the /source pattern/, the $/n/ expressions inside
the /target pattern/ correspond to the captured groups inside *source*.

Example:

Source: 123 source text
Target: 123 target text
Source pattern: ([0-9]+) source text
Target pattern: $1 target text

This yields a successful match since $1 in the /target pattern/ is replaced
by "123" from the first captured group in *source* and the resulting string,
"123 target text", matches the /target pattern/.

I would like to execute a query which for a given /source pattern/ and
/target pattern/ returns all rows from the *SEGMENT* table where *source*
matches the /source pattern/ and *target* matches the /target pattern/ after
it has its references replaced with the actual captured groups.

I believe this is not possible since *regexp_replace* expects a string as
its /replacement/ argument which is not enough in this case. This kind of
stuff is easy in e.g. C# where for regex replace you can provide a function
which receives the (in this case) reference index as its argument and you
can build the replacement string using external knowledge.

However, as I am no pro in Postgres, I may be missing something and
therefore I ask: is it possible to somehow mimic the behavior of
hypothetical *regexp_replace* which would accept a function of the
to-be-replaced value and would return the replacement string based on that.

And as I am thinking about it, even that would not suffice since that
function would need to access not only the to-be-replaced value but also the
corresponding source pattern match.

Still, isn't there some super clever way to do that?

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Emulating-flexible-regex-replace-tp5824034.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--

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

Daniel Begin | 23 Oct 12:38 2014
Picon

Re: Inconsistency between PgAdmin III GUI and SQL window ?

Thank Adrian,

I just found what went wrong in my script...
As described in the initial email, I set the search path to the destination
schema (xxx) prior to execute the script. Doing so, I was excluding the
public schema from the search and then cannot have access to PostGIS
extension. By setting the search path to xxx AND public schema (set
search_path to xxx, public;) everything goes right!

Daniel

-----Original Message-----
From: pgsql-general-owner <at> postgresql.org
[mailto:pgsql-general-owner <at> postgresql.org] On Behalf Of Adrian Klaver
Sent: October-22-14 09:46
To: Daniel Begin; 'David G Johnston'
Cc: pgsql-general <at> postgresql.org
Subject: Re: [GENERAL] Inconsistency between PgAdmin III GUI and SQL window
?

On 10/22/2014 03:25 AM, Daniel Begin wrote:
> David, Adrian,
> I am new to databases and since PgAdmin displays Catalogs, Event 
> Triggers, Extensions and Schema as "Child" of the database, I assumed 
> that Extensions were linked to a specific database, not to a specific
schema.

Yes and no. If they are installed in the pg_catalog schema then they are
available database wide:

http://www.postgresql.org/docs/9.3/static/ddl-schemas.html

In addition to public and user-created schemas, each database contains a
pg_catalog schema, which contains the system tables and all the built-in
data types, functions, and operators. pg_catalog is always effectively part
of the search path. If it is not named explicitly in the path then it is
implicitly searched before searching the path's schemas. This ensures that
built-in names will always be findable. However, you can explicitly place
pg_catalog at the end of your search path if you prefer to have user-defined
names override built-in names.

This is where you will find the procedural languages. So in psql you can do
the below to see what is installed and where:

test=# \dx
                                  List of installed extensions
    Name    | Version |   Schema   |                        Description 

-----------+---------+------------+-------------------------------------
-----------+---------+------------+-----------------------
  hstore    | 1.2     | public     | data type for storing sets of (key, 
value) pairs
  plpgsql   | 1.0     | pg_catalog | PL/pgSQL procedural language
  tablefunc | 1.0     | public     | functions that manipulate whole 
tables, including crosstab

>
> After reading your answers, I had another look at PostGIS extension 
> properties and it is pretty clear it belongs to the public schema, 
> which explains the error message I got.
>
> Thanks
> Daniel
>

--
Adrian Klaver
adrian.klaver <at> aklaver.com

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

--

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

Enrico Pirozzi | 22 Oct 17:18 2014
Picon

Index scan vs indexonly scan method

Hi,
I was working on this simple query

select field1 from table A
where A.field2 <= some_value
order by 1 desc limit some_value

and I saw that:

1) the planner on this query uses an index only scan method:

select field1 from table A
where A.field2 <= '2014-08-13 10:20:59.99648+02'
order by 1 desc limit 100

2) the planner on this query uses a classic index scan method:

select field1 from table A
where A.field2 <= '2014-08-13 10:20:59.99648+02'
order by 1 desc limit 1

the only difference between the two queries is the limit clause,
for the first query the limit is 100 and for the second the limit is 1

it seems a little bit strange...someone can help me to understand why?

My develop PostgreSQL version is a 9.4 beta

regards,
Enrico

-- 
That's one small step for man; one giant leap for mankind

Enrico Pirozzi
Tel.  +39 0861 1855771 - Mob.+39 328 4164437 - Fax  +39 0861 1850310
http://www.pgtraining.com <http://www.pgtraining.com-> -
info <at> pgtraining.com
www.enricopirozzi.info - info <at> enricopirozzi.info
PEC: enrico.pirozzi <at> pec.it
Skype sscotty71 - Gtalk sscotty71 <at> gmail.com

--

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

Postgres India | 22 Oct 12:45 2014
Picon

DBlink, postgres to DB2

Hi, 
I am trying to connect DB2 from postgres using dblink, is there any configuration required at DB2 and postgres server.

If any command string please provide it.



Thanks
Manmohan. K


Gmane