Updating postgres from 9.3 to 9.4 on MacOSX

November 28, 2015 at 11:45 pm · Filed under MacOSX, Postgresql, Ruby

Working with Heroku I recently wanted to pull the Heroku production DB to my local DB. Using pg:pull I got the following error message:
pg_dump: aborting because of server version mismatch
because Heroku uses PostgreSQL 9.4 and my local version was 9.3.
So I had to update my local DB; this is what I did:

1) shut down db and remove launch agent plists:
launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
rm ~/Library/LaunchAgents/homebrew.mxcl.postgresql*


2) remove links to old binaries, install new version (links automatically):
brew unlink postgresql93
brew install postgres


Note that I do not yet uninstall the old binaries, we need them to run pg_update later!

3) Link the new launch agent plist
ln -sfv /usr/local/opt/postgresql/*.plist ~/Library/LaunchAgents


4) create a new empty cluster with the same locale as the old one:
initdb /usr/local/var/postgres9.4 --locale=en_US.UTF-8


5) run the upgrade script (using the binary of the new version)
pg_upgrade -v \
-d /usr/local/var/postgres \
-D /usr/local/var/postgres9.4 \
-b /usr/local/Cellar/postgresql93/9.3.5/bin \
-B /usr/local/Cellar/postgresql/9.4.5_2/bin


6) start new server and uninstall the old version:
launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
brew uninstall postgresql93

7) optional: re-install the pg rem for my ruby app
gem uninstall pg
bundle

Hope this helps somebody!

Kommentare

Atomares Einfügen in PostgreSQL | Teil 2

Juni 24, 2009 at 9:59 pm · Filed under Postgresql

Die im vorletzten Artikel vorgestellte Lösung hat den Nachteil, den ganzen Table my_table zu locken.
Das ist dann ein Problem, wenn criterion mittels unique constraint eindeutig gemacht wird und nach der Funktion eine zeitaufwendige Funktion (z.B. eine Suche) in derselben Transaktion folgt: Dann warten nämlich alle Prozesse, die ein findOrCreate machen wollen, unabhängig mit welchem Wert für criterion, auf den Abschluss der ersten Transaktion, da erst zu diesem Zeitpunkt sichergestellt werden kann, dass die Spalte wirklich unique ist.

Eigentlich sollte es aber möglich sein, dass parallele Transaktionen mit anderen Werten für criterion unbehelligt ausgeführt werden.
Mit dem folgenden, von Martin Heistermann und Stephan Lüderitz vorgeschlagenen Ansatz, umgeht man dieses Problem elegant:

CREATE OR REPLACE FUNCTION findOrCreate(
  IN criterion_in text,
  OUT record_id integer, OUT is_new bool) AS
$$
DECLARE
BEGIN
    INSERT INTO
      my_table (criterion)
    VALUES
      (criterion_in)
    RETURNING id INTO record_id;
    SELECT true INTO is_new;
EXCEPTION
    WHEN unique_violation THEN
        SELECT false, id into is_new, record_id FROM my_table 
        WHERE criterion=criterion_in;
END
$$ language plpgsql;

Wie in dieser Präsentation (PDF) beschrieben, läßt PostgreSQL das parallele Einfügen verschiedener Werte mittels findOrCreate zu, zwingt aber bei zwei Transaktionen, die denselben Wert einfügen wollen, die zweite, mit dem Insert auf das Ende der ersten zu warten.

Findet die (potentiell teure) Suche in der Transaktion nach findOrCreate statt, wird diese gar nicht mehr ausgeführt, da findOrCreate per Exception die von der ersten Transaktion neu eingefügte Zeile ermittelt und gleich mit deren Ergebnissen weitermachen kann.

Kommentare

Atomares Einfügen in PostgreSQL

Mai 16, 2009 at 5:31 pm · Filed under Postgresql

Oft steht man vor dem Problem, einen Datensatz anhand eines Kriteriums zu finden bzw. ihn, falls nicht vorhanden, einzufügen. Bei derartigen Operationen ist es wichtig darauf zu achten, dass nicht mehrere Prozesse, die quasi gleichzeitig diesen Datensatz suchen, eine race condition hervorrufen.
Hier eine Lösung mittels stored procedure für postgresql. Dabei wird eine race condition zwischen Suche und Einfügen vermieden und nur minimales Locking gebraucht:

CREATE OR REPLACE FUNCTION findOrCreate(
  IN criterion_in text, 
  OUT id int, OUT is_new bool) AS 
$$
DECLARE 
BEGIN
  LOCK TABLE my_table;
  SELECT false, id FROM my_table WHERE criterion=criterion_in INTO is_new, id;

  IF id IS NULL THEN
    INSERT INTO 
      my_table (criterion) 
    VALUES 
      (criterion_in) 
    RETURNING id INTO id;
    
    SELECT true INTO is_new;
  END IF;

  RETURN;
END
$$ language plpgsql;

Man beachte die praktischen OUT-Parameter, die es ermöglichen, diese Funktion wie folgt aufzurufen:

SELECT id, is_new FROM findOrCreate(criterion);

Rückgabewerte sind id und is_new, die einem die Datenbank-Id angeben, und ob diese neu angelegt wurde.

Der Table-Lock wird beim Beenden der Funktion (die immer implizit eine Transaktion ist) automatisch beendet.

Kommentare

hstore magic

April 17, 2007 at 12:08 pm · Filed under Postgresql

A lot of nice things can be done with postgresql’s hstore type.

They are a nice way to store flags, for example, and by providing an aggregate function to sum them up, one can easily compute complex unions of properties.

First we define an aggregate function to sum postgresql hstores, straightforward as this:

CREATE AGGREGATE sum(
  sfunc     = hs_concat,
  basetype  = hstore,
  stype     = hstore,
  initcond  = ''
);

Allows for beautiful constructs as

CREATE OR REPLACE FUNCTION array_to_hstore(text[]) RETURNS hstore AS $$
  DECLARE
    arr     text[];
    result  hstore;
  BEGIN
    SELECT INTO arr $1;

    SELECT INTO result sum(key=>1) FROM (
      select
        btrim(arr[idx.i]) as key
      from
        generate_series(1, array_upper(arr,1)) as idx(i)
    ) as dummy;

    RETURN result;

  END
$$ LANGUAGE plpgsql;

Now what can be done with that? For example we can turn comma separated lists into hstores:

mydb=> SELECT array_to_hstore(string_to_array('a, b, c, a', ','));
              array_to_hstore              
-------------------------------------------
 "a"=>"1", "b"=>"1", "c"=>"1"
(1 Zeile)

Comments (1)