Re: [GENERAL] recovery of a windows archive in linux

[Guillaume Drolet]

Adrian, Jim: thanks a lot for your help. I'll replay my logs on a Windows
machine very similar to the one that was hosting my db and see how it goes
before I make a pg_dump for Linux.

2015-04-13 21:49 GMT-04:00 Adrian Klaver adrian.kla...@aklaver.com:

 On 04/13/2015 04:37 PM, Guillaume Drolet wrote:

 Dear list,

 I have a base backup and archive logs from a Windows 7 PGSQL 9.3
 install. The machine OS disk started to show signs of failure so I
 replaced it and now I want to switch for a Linux system.

 My question is: will I be able to play my archive logs and point-in-time
 recover on the Linux machine?


 No. The machines have to be the same OS/architecture.



 That would spare some time rebuilding a Windows machine, recovering my
 db, and dumping for a Linux box.

 Thanks for your advice.

 Guillaume



 --
 Adrian Klaver
 adrian.kla...@aklaver.com

[GENERAL] recovery of a windows archive in linux

[Guillaume Drolet]

Dear list,

I have a base backup and archive logs from a Windows 7 PGSQL 9.3 install.
The machine OS disk started to show signs of failure so I replaced it and
now I want to switch for a Linux system.

My question is: will I be able to play my archive logs and point-in-time
recover on the Linux machine?

That would spare some time rebuilding a Windows machine, recovering my db,
and dumping for a Linux box.

Thanks for your advice.

Guillaume

[GENERAL] Slow trigger on insert: alternative solutions?

[Guillaume Drolet]

Hello,

I need your help speeding up the procedure I will explain below. I am
looking for improvements to my method or different approaches/ideas to
would help in this matter.

I have a set of DBF files that I load into my database using a plpython
function and a call to ogr2ogr (http://www.gdal.org/drv_pg.html). Once in a
while, I'll have to load updated versions of these tables to get the latest
additions and possible corrections to older versions.

In my plpython script, if a table is loaded for the first time, I first
load it empty, then I create a trigger function on insert (execute on row)
that will check for duplicates on each insert. Depending on the type of
data I load, my trigger first checks for equality in a subset of columns
(between 1 and 3 columns that would be like my primary key(s)) and if true,
I check if all columns are equal between NEW and the matching row from my
table. When this condition is true, I return null, else I store rows (i.e.
NEW.* and matching row(s) in a new table called duplicate for further
manual investigation. Here's an example for one table:

CREATE OR REPLACE FUNCTION check_naipf_insert()
  RETURNS trigger AS
' BEGIN
   IF EXISTS (SELECT 1
FROMpublic.naipf
WHERE id_pet_mes IS NOT DISTINCT FROM NEW.id_pet_mes
AND etage IS NOT DISTINCT FROM NEW.etage) THEN
  IF EXISTS (SELECT 1
   FROM public.naipf
   WHERE id_pet_mes IS NOT DISTINCT FROM NEW.id_pet_mes
   AND etage IS NOT DISTINCT FROM NEW.etage
   AND type_couv IS NOT DISTINCT FROM NEW.type_couv
   AND densite IS NOT DISTINCT FROM NEW.densite
   AND hauteur IS NOT DISTINCT FROM NEW.hauteur
   AND cl_age IS NOT DISTINCT FROM NEW.cl_age) THEN
 RETURN NULL;
   ELSE
 INSERT INTO public.duplic_naipf SELECT NEW.*;
 INSERT INTO public.duplic_naipf (SELECT *
   FROM
public.naipf
   WHERE id_pet_mes
IS NOT DISTINCT FROM  NEW.id_pet_mes
AND etage IS
NOT DISTINCT FROM NEW.etage );
 RETURN NULL;
   END IF;
 END IF;
 RETURN NEW;
 END;  '
 LANGUAGE plpgsql VOLATILE COST 100;

CREATE TRIGGER check_insert_naipf
  BEFORE INSERT
  ON public.pet4_naipf
  FOR EACH ROW
  EXECUTE PROCEDURE check_naipf_insert();

(in this case, duplicate rows that need investigation are rows that may
have changed relative to older version of the DBF file, but that have no
change in what I call their primary keys although they are not really PKs
since I don't want to raise errors at loading)

Once this is done, ogr2ogr is called a second time to load the data. It is
quite fast for small tables (tens of thousands of rows, tens of columns)
but for large tables it takes forever. For example, I started loading a
table with 3.5 million rows/33 columns last Friday at 3PM and this now,
Monday morning at 9PM some 3 million rows have been loaded.

My question is: what are the other approaches that would make this
procedure faster? How is this kind of task usually implemented in
postgresql? Would it be better to load everything with no check and then
apply some functions to find duplicate rows (although this would involve
more manual work)?

Thanks a lot for your help!

Re: [GENERAL] Slow trigger on insert: alternative solutions?

[Guillaume Drolet]

Thanks Adrian,

In option 2, when you say this is automated in an external Python script,
do you mean that you use something like psycopg2 to perform the queries the
database (e.g. for comparing data in the holding table with the older
table)?

Thanks.

2015-03-30 9:53 GMT-04:00 Adrian Klaver adrian.kla...@aklaver.com:

 On 03/30/2015 06:04 AM, Guillaume Drolet wrote:

 Hello,

 I need your help speeding up the procedure I will explain below. I am
 looking for improvements to my method or different approaches/ideas to
 would help in this matter.

 I have a set of DBF files that I load into my database using a plpython
 function and a call to ogr2ogr (http://www.gdal.org/drv_pg.html). Once
 in a while, I'll have to load updated versions of these tables to get
 the latest additions and possible corrections to older versions.

 In my plpython script, if a table is loaded for the first time, I first
 load it empty, then I create a trigger function on insert (execute on
 row) that will check for duplicates on each insert. Depending on the
 type of data I load, my trigger first checks for equality in a subset of
 columns (between 1 and 3 columns that would be like my primary key(s))
 and if true, I check if all columns are equal between NEW and the
 matching row from my table. When this condition is true, I return null,
 else I store rows (i.e. NEW.* and matching row(s) in a new table called
 duplicate for further manual investigation. Here's an example for one
 table:

 CREATE OR REPLACE FUNCTION check_naipf_insert()
RETURNS trigger AS
 ' BEGIN
 IF EXISTS (SELECT 1
  FROMpublic.naipf
  WHERE id_pet_mes IS NOT DISTINCT FROM NEW.id_pet_mes
  AND etage IS NOT DISTINCT FROM NEW.etage) THEN
IF EXISTS (SELECT 1
 FROM public.naipf
 WHERE id_pet_mes IS NOT DISTINCT FROM
 NEW.id_pet_mes
 AND etage IS NOT DISTINCT FROM NEW.etage
 AND type_couv IS NOT DISTINCT FROM NEW.type_couv
 AND densite IS NOT DISTINCT FROM NEW.densite
 AND hauteur IS NOT DISTINCT FROM NEW.hauteur
 AND cl_age IS NOT DISTINCT FROM NEW.cl_age) THEN
   RETURN NULL;
 ELSE
   INSERT INTO public.duplic_naipf SELECT NEW.*;
   INSERT INTO public.duplic_naipf (SELECT *
 FROM
 public.naipf
 WHERE
 id_pet_mes IS NOT DISTINCT FROM  NEW.id_pet_mes
  AND etage
 IS NOT DISTINCT FROM NEW.etage );
   RETURN NULL;
 END IF;
   END IF;
   RETURN NEW;
   END;  '
   LANGUAGE plpgsql VOLATILE COST 100;

 CREATE TRIGGER check_insert_naipf
BEFORE INSERT
ON public.pet4_naipf
FOR EACH ROW
EXECUTE PROCEDURE check_naipf_insert();

 (in this case, duplicate rows that need investigation are rows that may
 have changed relative to older version of the DBF file, but that have no
 change in what I call their primary keys although they are not really
 PKs since I don't want to raise errors at loading)

 Once this is done, ogr2ogr is called a second time to load the data. It
 is quite fast for small tables (tens of thousands of rows, tens of
 columns) but for large tables it takes forever. For example, I started
 loading a table with 3.5 million rows/33 columns last Friday at 3PM and
 this now, Monday morning at 9PM some 3 million rows have been loaded.

 My question is: what are the other approaches that would make this
 procedure faster? How is this kind of task usually implemented in
 postgresql? Would it be better to load everything with no check and then
 apply some functions to find duplicate rows (although this would involve
 more manual work)?


 I guess it depends on what end purpose of the above is? If you are just
 trying to keep relatively update to date information from the DBF sources,
 would it not be easier just to load them into a new table?

 So, where existing table is some_dbf_data:

 1) CREATE TABLE new_some_dbf_data(...)
 2) Dump DBF file into new_some_dbf_data
 3)In transaction rename/drop some_dbf_data, rename new_some_dbf_data to
 some_dbf_data


 Option 2 is what I do for a similar procedure:

 1) Dump DBF data into holding table.
 2) Use SQL in function(s) to compare old/new table and make appropriate
 adjustments. Doing SQL in bulk is a lot faster then checking each row, or
 least that is what I found. In any case the way you are doing it looks to
 involve 3.5 million inserts with a trigger action on each, that is bound to
 be slow:)
 3) This is automated in an external Python script.

 Option 3

 Use dbf(https://pypi.python.org/pypi/dbf/0.88.16) and do the comparisons
 in the DBF files outside Postgres and only import what has changed.





  Thanks

Re: [GENERAL] Sequences not moved to new tablespace

[Guillaume Drolet]

2015-02-24 7:07 GMT-05:00 Guillaume Drolet droletguilla...@gmail.com:



 2015-02-23 14:14 GMT-05:00 Adrian Klaver adrian.kla...@aklaver.com:

 On 02/23/2015 10:08 AM, Guillaume Drolet wrote:

 Hello,

 I moved all my tables and indexes from one tablespace to pg_default using

 ALTER TABLE ... SET TABLESPACE pg_default;
 ALTER INDEX ... SET TABLESPACE pg_default;

 Some 2500 files were moved to pg_default but 461 files remain in the
 tablespace and so I cannot drop it.

 When I query, for example:

 SELECT oid, relname, relkind FROM pg_catalog.pg_class
 WHERE oid IN (943602, 2650968, 2650971);

 I see that most of these files are sequences. Why didn't they get moved
 and how can I move them to pg_default (and all other remaining files) so
 that I can drop the tablespace?


 Well round file my previous suggestion. Just tried it and it did not work.


 Thanks Adrian.
 So, anybody else have some piece of advice on this?


 Thanks!


Digging a little more, I found that not only sequences were not moved but
also many tables in pg_catalog are still in my old tablespace. This is
expected since the query in the SQL files I used to move the tables and
indexes had a WHERE clause like this:


SELECT ' ALTER TABLE ' || schemaname || '.' || tablename || ' SET
TABLESPACE pg_default;'
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema');

So I tried removing the WHERE clause and running the script again:

psql -U postgres -d mydb  move_tables_to_pg_default.sql | findstr /R
/C:[ALTER] | psql -d mydb -U postgres

I got many errors like this one:

ERROR:  permission denied: pg_event_trigger is a system catalog

If I can't move tables from pg_catalog, how will I be able to drop that
tablespace I don't want to use anymore?

I am thinking that maybe using ALTER DATABASE mydb SET TABLESPACE
pg_default; instead would take care of all this, no?

But when I tried it last week, I got a message like: some relations already
in target tablespace...

Any help will be much appreciated.





 --
 Adrian Klaver
 adrian.kla...@aklaver.com

Re: [GENERAL] Sequences not moved to new tablespace

[Guillaume Drolet]

2015-02-23 14:14 GMT-05:00 Adrian Klaver adrian.kla...@aklaver.com:

 On 02/23/2015 10:08 AM, Guillaume Drolet wrote:

 Hello,

 I moved all my tables and indexes from one tablespace to pg_default using

 ALTER TABLE ... SET TABLESPACE pg_default;
 ALTER INDEX ... SET TABLESPACE pg_default;

 Some 2500 files were moved to pg_default but 461 files remain in the
 tablespace and so I cannot drop it.

 When I query, for example:

 SELECT oid, relname, relkind FROM pg_catalog.pg_class
 WHERE oid IN (943602, 2650968, 2650971);

 I see that most of these files are sequences. Why didn't they get moved
 and how can I move them to pg_default (and all other remaining files) so
 that I can drop the tablespace?


 Well round file my previous suggestion. Just tried it and it did not work.


 Thanks Adrian.
So, anybody else have some piece of advice on this?


 Thanks!



 --
 Adrian Klaver
 adrian.kla...@aklaver.com

Re: [GENERAL] Sequences not moved to new tablespace

[Guillaume Drolet]

2015-02-24 8:45 GMT-05:00 Albe Laurenz laurenz.a...@wien.gv.at:

 Guillaume Drolet wrote:
  Digging a little more, I found that not only sequences were not moved
 but also many tables in
  pg_catalog are still in my old tablespace. This is expected since the
 query in the SQL files I used to
  move the tables and indexes had a WHERE clause like this:
 
  SELECT ' ALTER TABLE ' || schemaname || '.' || tablename || ' SET
 TABLESPACE pg_default;'
  FROM pg_tables
  WHERE schemaname NOT IN ('pg_catalog', 'information_schema');
 
  So I tried removing the WHERE clause and running the script again:
  psql -U postgres -d mydb  move_tables_to_pg_default.sql | findstr /R
 /C:[ALTER] | psql -d mydb -U
  postgres
 
  I got many errors like this one:
  ERROR:  permission denied: pg_event_trigger is a system catalog
 
  If I can't move tables from pg_catalog, how will I be able to drop that
 tablespace I don't want to use
  anymore?
 
  I am thinking that maybe using ALTER DATABASE mydb SET TABLESPACE
 pg_default; instead would take
  care of all this, no?
 
  But when I tried it last week, I got a message like: some relations
 already in target tablespace...
 
  Any help will be much appreciated.

 If you want to move a whole database to a different tablespace (the only
 reason
 I can think of for doing what you are trying to so), use the command
 ALTER DATABASE ... SET TABLESPACE ...


Thanks Laurenz. I tried your suggestion:

psql -U postgres -c ALTER DATABASE mydb SET TABLESPACE pg_default;

I get this message:

ERROR:  some relations of database mortalite are already in tablespace
pg_default
HINT : You must move them back to the database's default tablespace before
using this command.

But if I do SHOW default_tablespace; in mydb, it showed pg_default as
the default tablespace.

So I tried changing it back to the tablespace I want to get rid of to
subsequently moved everything back there so that ultimately, it lets me
move everything to pg_default:

ALTER DATABASE mydb SET default_tablespace = diamonds;

And then:

psql -U postgres -c ALTER DATABASE mydb SET TABLESPACE diamonds;

ALTER DATABASE is issued but nothing gets physically moved to diamonds. Why?



 Yours,
 Laurenz Albe

Re: [GENERAL] Sequences not moved to new tablespace

[Guillaume Drolet]

2015-02-24 10:06 GMT-05:00 Adrian Klaver adrian.kla...@aklaver.com:

 On 02/24/2015 05:06 AM, Guillaume Drolet wrote:



 2015-02-24 7:07 GMT-05:00 Guillaume Drolet droletguilla...@gmail.com
 mailto:droletguilla...@gmail.com:



 2015-02-23 14:14 GMT-05:00 Adrian Klaver adrian.kla...@aklaver.com
 mailto:adrian.kla...@aklaver.com:


 On 02/23/2015 10:08 AM, Guillaume Drolet wrote:

 Hello,

 I moved all my tables and indexes from one tablespace to
 pg_default using

 ALTER TABLE ... SET TABLESPACE pg_default;
 ALTER INDEX ... SET TABLESPACE pg_default;

 Some 2500 files were moved to pg_default but 461 files
 remain in the
 tablespace and so I cannot drop it.

 When I query, for example:

 SELECT oid, relname, relkind FROM pg_catalog.pg_class
 WHERE oid IN (943602, 2650968, 2650971);

 I see that most of these files are sequences. Why didn't
 they get moved
 and how can I move them to pg_default (and all other
 remaining files) so
 that I can drop the tablespace?


 Well round file my previous suggestion. Just tried it and it did
 not work.


 Thanks Adrian.
 So, anybody else have some piece of advice on this?


 Thanks!


 Digging a little more, I found that not only sequences were not moved
 but also many tables in pg_catalog are still in my old tablespace. This
 is expected since the query in the SQL files I used to move the tables
 and indexes had a WHERE clause like this:


 SELECT ' ALTER TABLE ' || schemaname || '.' || tablename || ' SET
 TABLESPACE pg_default;'
 FROM pg_tables
 WHERE schemaname NOT IN ('pg_catalog', 'information_schema');

 So I tried removing the WHERE clause and running the script again:

 psql -U postgres -d mydb  move_tables_to_pg_default.sql | findstr /R
 /C:[ALTER] | psql -d mydb -U postgres

 I got many errors like this one:

 ERROR:  permission denied: pg_event_trigger is a system catalog

 If I can't move tables from pg_catalog, how will I be able to drop that
 tablespace I don't want to use anymore?

 I am thinking that maybe using ALTER DATABASE mydb SET TABLESPACE
 pg_default; instead would take care of all this, no?

 But when I tried it last week, I got a message like: some relations
 already in target tablespace...

 Any help will be much appreciated.


 So how did all this stuff get into the non-default tablespace in the first
 place?


Stuff got there using the ALTER TABLE.../ALTER INDEX... SQL files
mentionned in my previous post.


 The answer would seem to be just reverse whatever you did in answer to the
 question above.


That makes sense. I will give it a try. Thanks.







 --
 Adrian Klaver
 adrian.kla...@aklaver.com mailto:adrian.kla...@aklaver.com





 --
 Adrian Klaver
 adrian.kla...@aklaver.com

Re: [GENERAL] Sequences not moved to new tablespace

[Guillaume Drolet]

2015-02-24 10:39 GMT-05:00 Albe Laurenz laurenz.a...@wien.gv.at:

 Guillaume Drolet wrote:
  If you want to move a whole database to a different tablespace (the
 only reason
  I can think of for doing what you are trying to so), use the command
  ALTER DATABASE ... SET TABLESPACE ...

  Thanks Laurenz. I tried your suggestion:
 
  psql -U postgres -c ALTER DATABASE mydb SET TABLESPACE pg_default;
 
  I get this message:
  ERROR:  some relations of database mortalite are already in tablespace
 pg_default
  HINT : You must move them back to the database's default tablespace
 before using this command.
 
  But if I do SHOW default_tablespace; in mydb, it showed pg_default
 as the default tablespace.
 
  So I tried changing it back to the tablespace I want to get rid of to
 subsequently moved everything
  back there so that ultimately, it lets me move everything to pg_default:
  ALTER DATABASE mydb SET default_tablespace = diamonds;
 
  And then:
  psql -U postgres -c ALTER DATABASE mydb SET TABLESPACE diamonds;
 
  ALTER DATABASE is issued but nothing gets physically moved to diamonds.
 Why?

 I guess the problem is that you already moved a lot of tables around.

 Could you connect to the database and try the following:

 SELECT d.datname, d.oid, sp.spcname, sp.oid
 FROM pg_tablespace sp JOIN
  pg_database d ON sp.oid = d.dattablespace
 WHERE datname = current_database();


  datname  |  oid   | spcname  |  oid
---++--+
 mydb| 942258 | diamonds | 940585
(1 row)



 and

 SELECT t.relname, t.reltablespace, sp.spcname
 FROM pg_class t LEFT JOIN
  pg_tablespace sp ON sp.oid = t.reltablespace;



 relname  | reltablespace
|  spcname
--+---+
 geography_columns| 0 |
 geometry_dump| 0 |
 pg_statistic | 0 |
 indexbdtq_wgs84_gid_seq  | 0 |
 mod09a1_sur_refl_b05_amonth_idx  |  1663 |
pg_default
 mod44b_cloud_rid_seq | 0 |
 pg_toast_2619| 0 |
 pg_type  | 0 |
 pg_authid_rolname_index  |  1664 |
pg_global
 pg_authid_oid_index  |  1664 |
pg_global
 valid_detail | 0 |
 pg_roles | 0 |
 pg_shadow| 0 |
 pg_group | 0 |
 pg_inherits_parent_index | 0 |
 pg_toast_1255| 0 |
 pg_database_datname_index|  1664 |
pg_global
 pg_database_oid_index|  1664 |
pg_global
 pg_am_name_index | 0 |
 pg_am_oid_index  | 0 |
 pg_amop_fam_strat_index  | 0 |
 pg_amop_opr_fam_index| 0 |
 pg_amop_oid_index| 0 |
 pg_amproc_fam_proc_index | 0 |
 pg_amproc_oid_index  | 0 |
 pg_aggregate_fnoid_index | 0 |
 pg_toast_2618| 0 |
 pg_toast_2618_index  | 0 |
 pg_toast_2609| 0 |
 pg_toast_2609_index  | 0 |
 pg_cast_oid_index| 0 |
 pg_cast_source_target_index  | 0 |
 pg_toast_2964|  1664 |
pg_global
 pg_toast_2964_index  |  1664 |
pg_global
 pg_auth_members_role_member_index|  1664 |
pg_global
 pg_auth_members_member_role_index|  1664 |
pg_global
 pg_toast_3596| 0 |
 pg_toast_3596_index  | 0 |
 pg_collation_oid_index   | 0 |
 pg_collation_name_enc_nsp_index  | 0 |
 pg_toast_2604

Re: [GENERAL] Sequences not moved to new tablespace

[Guillaume Drolet]

2015-02-24 10:18 GMT-05:00 Adrian Klaver adrian.kla...@aklaver.com:

 On 02/24/2015 07:10 AM, Guillaume Drolet wrote:






 So how did all this stuff get into the non-default tablespace in the
 first place?


 Stuff got there using the ALTER TABLE.../ALTER INDEX... SQL files
 mentionned in my previous post.


 The answer would seem to be just reverse whatever you did in answer
 to the question above.


 That makes sense. I will give it a try. Thanks.


 Alright, now I am thoroughly confused:) I thought this is how you to this
 point, using the above commands to move from the non-default tablespace
 back to the default tablespace:

 I moved all my tables and indexes from one tablespace to pg_default using

 ALTER TABLE ... SET TABLESPACE pg_default;
 ALTER INDEX ... SET TABLESPACE pg_default;

 And that the issue was that sequences where not moved back.


That was the issue described in my original post, i.e. that tables and
indexes were moved to pg_default but that sequences and tables from
pg_catalog did not follow.

I'm now trying to figure how to really do it!










 --
 Adrian Klaver
 adrian.kla...@aklaver.com

Re: [GENERAL] Sequences not moved to new tablespace

[Guillaume Drolet]

2015-02-24 10:32 GMT-05:00 Tom Lane t...@sss.pgh.pa.us:

 Adrian Klaver adrian.kla...@aklaver.com writes:
  On 02/24/2015 07:10 AM, Guillaume Drolet wrote:
  That makes sense. I will give it a try. Thanks.

  Alright, now I am thoroughly confused:) I thought this is how you to
  this point, using the above commands to move from the non-default
  tablespace back to the default tablespace:

  I moved all my tables and indexes from one tablespace to pg_default
 using

  ALTER TABLE ... SET TABLESPACE pg_default;
  ALTER INDEX ... SET TABLESPACE pg_default;

  And that the issue was that sequences where not moved back.

 I think part of the issue here is confusion over what default means.
 pg_default refers to an installation's default tablespace, to wit
 storage under the $PGDATA directory.  This is not necessarily the
 same thing as a database's default tablespace, which might have been
 set to something else.

 We now know why Guillaume was having a problem with sequences: he built
 his movement script on the basis of the pg_tables view, which does not
 include sequences.  But in any case, if I'm understanding his desires
 correctly, changing the database's default tablespace would have been
 far easier and more reliable than manually moving tables one at a time.

 For implementation reasons, ALTER DATABASE SET TABLESPACE refuses the
 case where the database already has some tables that have been explicitly
 placed into that tablespace.  (I forget the exact reason for this, but
 it's got something to do with needing to preserve a distinction between
 tables that have had a tablespace explicitly assigned and those that
 are just inheriting the database's default tablespace.)  So the best
 bet at this point seems to be to move everything back to the database's
 original tablespace and then use ALTER DATABASE SET TABLESPACE.

 If you're not sure what remains to move, try looking at the
 pg_class.reltablespace column.  There will be a few entries with
 tablespace 1664 (pg_global) which you can't and shouldn't move.
 You want everything else to be shown as tablespace 0, which means
 use the database's default.

 regards, tom lane


Thanks a lot Tom for this very useful clarification. I am indeed trying to
move everything from a created tablespace to the installation's default
directory (pg_default) under $PGDATA. When I first created the database I
want to move, I had set the default tablespace to my created tablespace,
diamonds.

Now with your explanations I understand why sequences and other tables
where not moved: because of the pg_tables view. Based on these details,
here's what I understand I should do now:

1) Run the SQL scripts for moving tables and indexes back to diamonds
3) Set database default tablespace to diamonds: ALTER DATABASE SET
default_tablespace = diamonds;
2) Check that nothing else but entries with reltablespace equal to 1664
remain in pg_class. Everything else should equal 0
4) Move the database to pg_default with: ALTER DATABASE mydb SET TABLESPACE
pg_default;
5) DROP TABLESPACE diamonds;

Is this correct?

[GENERAL] Sequences not moved to new tablespace

[Guillaume Drolet]

Hello,

I moved all my tables and indexes from one tablespace to pg_default using

ALTER TABLE ... SET TABLESPACE pg_default;
ALTER INDEX ... SET TABLESPACE pg_default;

Some 2500 files were moved to pg_default but 461 files remain in the
tablespace and so I cannot drop it.

When I query, for example:

SELECT oid, relname, relkind FROM pg_catalog.pg_class
WHERE oid IN (943602, 2650968, 2650971);

I see that most of these files are sequences. Why didn't they get moved and
how can I move them to pg_default (and all other remaining files) so that I
can drop the tablespace?

Thanks!

Re: [GENERAL] Cluster seems broken after pg_basebackup

[Guillaume Drolet]

2015-02-09 16:10 GMT-05:00 Jim Nasby jim.na...@bluetreble.com:

 On 2/9/15 11:51 AM, Guillaume Lelarge wrote:

 According to this page
 https://msdn.microsoft.com/en-ca/library/cc704588.aspx, exception
 0xC005 means STATUS_ACCESS_VIOLATION - The instruction at 0x%08lx
 referenced memory at 0x%08lx. The memory could not be %s. This is not of
 much help to me.


 In my experience that means that your data is corrupted.

  I hope these additional bits of information can help someone figuring
 out a solution to get my cluster up and running again.

 PS. I was thinking of reinstalling PGSQL over my current install but
 keeping my PGDATA. I've done it in the past for fixing problems with
 starting the service and it worked. What do you think?


 You could try it, but as Guillaume Drolet mentioned I don't see this
 helping.

 Since this is happening on your original database, I suspect that's what's
 been corrupted. In my experience, this means you either have faulty
 hardware, or there's a misconfiguration that means fsync isn't doing what
 it's supposed to do.


For those interested, I reinstalled the PGSQL binaries over, keeping my
PGDATA and tablespace. It stopped the crashes mentioned in the previous
posts, at least for now. We'll see if the crashes come back.

 --
 Jim Nasby, Data Architect, Blue Treble Consulting
 Data in Trouble? Get it in Treble! http://BlueTreble.com

Re: [GENERAL] Starting new cluster from base backup

[Guillaume Drolet]

2015-02-18 13:40 GMT-05:00 Adrian Klaver adrian.kla...@aklaver.com:

 On 02/18/2015 10:24 AM, Guillaume Drolet wrote:



 2015-02-18 11:06 GMT-05:00 Adrian Klaver adrian.kla...@aklaver.com
 mailto:adrian.kla...@aklaver.com:



 So is E:\ a network drive shared by both machines?


 No, E:\ is a local drive on which I created a tablespace, in order to
 have enough space for my database. In my current setup on the source
 machine, PGDATA is in the default PGSQL installation on the OS disk so
 space is limited. On the destination machine, PGDATA will be on a
 different, larger disk than the OS disk.


 So is there an E:\ drive available on the destination machine?


Yes there is an E:\ drive available on the destination machine. But for
now, these two machines don't communicate. I take the backup on a hot swap
disk (F:\) and then swap it into the destination machine. Ultimately when
my database will be running on the destination machine, I'll connect to it
from other machines in my local network.






 Anyway, in the end I want to move the database that's in that
 tablespace
 back to pg_default. I see two possibilities:

 1) Moving it now, before taking the base backup, using ALTER
 DATABASE
 mydb SET TABLESPACE pg_default; Then I assume I should be able
 to use -X
 stream and plain format with pg_basebackup.

 Or

 2) Delete the symbolic link in data/pg_tblspc, use pg_basebackup
 with -X
 stream and plain format, copy the tablespace from the source to
 the
 destination machine. Create a new symbolic link in
 data/pg_tblspc on the
 new machine and point it to the copied tablespace.

 Are these two approaches feasible?


 I would say 1 would be more feasible then 2. If you use 2, delete
 the symlink and do the backup, what happens with any dependencies
 between objects in the default tablespace and the one you cut out?
 Also the pg_basebackup will be taking a backup of one part of the
 cluster at one point in time and the copy of the remote tablespace
 will possibly be at another point in time. I do no see that ending
 well.


 You're probably right about that. My understanding was that, since this
 is a single-user database (at least for now) on my machine, if I wasn't
 performing any query or task during the backup, then the problem you
 mentioned would in fact not be a problem.


 Except Postgres performs tasks behind the scenes, so changes are
 happening. There is also still the dependency issue.


Can't the dependency issue be fixed by creating a new junction in
data/pg_tblspc that would point to the relocated tablespace?










  Thanks.



 --
 Adrian Klaver
 adrian.kla...@aklaver.com

Re: [GENERAL] Starting new cluster from base backup

[Guillaume Drolet]

2015-02-18 11:06 GMT-05:00 Adrian Klaver adrian.kla...@aklaver.com:

 On 02/18/2015 04:26 AM, Guillaume Drolet wrote:



 2015-02-17 17:14 GMT-05:00 Adrian Klaver adrian.kla...@aklaver.com
 mailto:adrian.kla...@aklaver.com:


 On 02/17/2015 06:54 AM, Guillaume Drolet wrote:

 Adrian: thanks for this information.

 I tried running pg_basebackup in plain format with option -X
 stream
 (pg_basebackup -D F:\208376PT\db -X stream -l
 208376PT17022015 -U
 postgres -P) but I got the message:

 pg_basebackup: directory E:\Data\Database exists but is not
 empty

 I creatde a tablespace using CREATE TABLESPACE at the location
 mentioned
 in the message. According to what I read online about this, this
 message
 is issued when a tablespace was created under PGDATA. In my
 case, only
 the directory junction pointing to my tablespace (on a different
 drive
 than PGDATA) exists under PGDATA, not the tablespace itself.

 The only way I can run pg_basebackup with WAL files is with
 option -Ft
 and -X fetch. I'd much prefer using plain mode since my 670 GB
 tablespace takes a lot of time to extract when tarred. Is there
 another
 way to approach this?


 All I can come up with at the moment

 So what is the path on the original machine and can it be replicated
 on the new machine, at least temporarily?


 The path on the original (i.e. source) machine is:
 E:\Data\Database\PG_9.3_201306121\..


 I'm thinking if the path can be replicated, let pg_basebackup write
 to it and then create the tablespace you want and do ALTER TABLE SET
 TABLESPACE to move the tables. You would also need to do this for
 indexes.


 Not sure I understand when you say let pg_basebackup write to it. This
 tablespace already exists on the source machine so cannot be written
 over. It needs to be written in the backup so that I can than recreate
 it on the destination machine.


 So is E:\ a network drive shared by both machines?


No, E:\ is a local drive on which I created a tablespace, in order to have
enough space for my database. In my current setup on the source machine,
PGDATA is in the default PGSQL installation on the OS disk so space is
limited. On the destination machine, PGDATA will be on a different, larger
disk than the OS disk.



 Anyway, in the end I want to move the database that's in that tablespace
 back to pg_default. I see two possibilities:

 1) Moving it now, before taking the base backup, using ALTER DATABASE
 mydb SET TABLESPACE pg_default; Then I assume I should be able to use -X
 stream and plain format with pg_basebackup.

 Or

 2) Delete the symbolic link in data/pg_tblspc, use pg_basebackup with -X
 stream and plain format, copy the tablespace from the source to the
 destination machine. Create a new symbolic link in data/pg_tblspc on the
 new machine and point it to the copied tablespace.

 Are these two approaches feasible?


 I would say 1 would be more feasible then 2. If you use 2, delete the
 symlink and do the backup, what happens with any dependencies between
 objects in the default tablespace and the one you cut out? Also the
 pg_basebackup will be taking a backup of one part of the cluster at one
 point in time and the copy of the remote tablespace will possibly be at
 another point in time. I do no see that ending well.


You're probably right about that. My understanding was that, since this is
a single-user database (at least for now) on my machine, if I wasn't
performing any query or task during the backup, then the problem you
mentioned would in fact not be a problem.







 Thanks.




 --
 Adrian Klaver
 adrian.kla...@aklaver.com mailto:adrian.kla...@aklaver.com




 --
 Adrian Klaver
 adrian.kla...@aklaver.com

Re: [GENERAL] Starting new cluster from base backup

[Guillaume Drolet]

2015-02-18 16:11 GMT-05:00 Adrian Klaver adrian.kla...@aklaver.com:

 On 02/18/2015 11:51 AM, Guillaume Drolet wrote:



 2015-02-18 13:40 GMT-05:00 Adrian Klaver adrian.kla...@aklaver.com
 mailto:adrian.kla...@aklaver.com:

 On 02/18/2015 10:24 AM, Guillaume Drolet wrote:



 2015-02-18 11:06 GMT-05:00 Adrian Klaver
 adrian.kla...@aklaver.com mailto:adrian.kla...@aklaver.com
 mailto:adrian.klaver@aklaver.__com
 mailto:adrian.kla...@aklaver.com:



  So is E:\ a network drive shared by both machines?


 No, E:\ is a local drive on which I created a tablespace, in
 order to
 have enough space for my database. In my current setup on the
 source
 machine, PGDATA is in the default PGSQL installation on the OS
 disk so
 space is limited. On the destination machine, PGDATA will be on a
 different, larger disk than the OS disk.


 So is there an E:\ drive available on the destination machine?


 Yes there is an E:\ drive available on the destination machine. But for
 now, these two machines don't communicate. I take the backup on a hot
 swap disk (F:\) and then swap it into the destination machine.
 Ultimately when my database will be running on the destination machine,
 I'll connect to it from other machines in my local network.


 So if I understand correctly you have:

 1) On source machine a directory E:\Data\Database.
 2) On the source machine in Postgres you have a created a tablespace that
 points at E:\Data\Database.
 3) On destination machine you have an E:\ drive also.

 You're correct


 Then have you tried:

 1) Create \Data\Database directory under E:\ on the destination machine.

2) Do the pg_basebackup.


I'm not sure I understand why, at this moment in the sequence of operation,
I would create \Data\Database under E:\ on the destination machine.
pg_basebackup, when run on the source DB on the source machine, has no idea
about the destination machine. Maybe you're confused with the F:\ drive,
which is the drive on which I tried to save my base backup with the command:

pg_basebackup -D F:\208376PT\db -X stream -l 208376PT17022015 -U
postgres -P

This drive (F:\) is not the destination machine, it's a swappable disk I
use to move my base backup from one machine (the source) to another (the
destination).




 Can't the dependency issue be fixed by creating a new junction in
 data/pg_tblspc that would point to the relocated tablespace?


 The docs say you can:

 http://www.postgresql.org/docs/9.3/static/manage-ag-tablespaces.html

 The directory $PGDATA/pg_tblspc contains symbolic links that point to
 each of the non-built-in tablespaces defined in the cluster. Although not
 recommended, it is possible to adjust the tablespace layout by hand by
 redefining these links. Under no circumstances perform this operation while
 the server is running. Note that in PostgreSQL 9.1 and earlier you will
 also need to update the pg_tablespace catalog with the new locations. (If
 you do not, pg_dump will continue to output the old tablespace locations.)


 I have not done it and I see the Although not recommended.. part above,
 so I would say that is a last resort solution.


I confirm this method works. I've done it in the past using the steps in
this blog and its comments:

http://www.databasesoup.com/2013/11/moving-tablespaces.html













   Thanks.



 --
 Adrian Klaver
 adrian.kla...@aklaver.com

Re: [GENERAL] Starting new cluster from base backup

[Guillaume Drolet]

2015-02-17 17:14 GMT-05:00 Adrian Klaver adrian.kla...@aklaver.com:

 On 02/17/2015 06:54 AM, Guillaume Drolet wrote:

 Adrian: thanks for this information.

 I tried running pg_basebackup in plain format with option -X stream
 (pg_basebackup -D F:\208376PT\db -X stream -l 208376PT17022015 -U
 postgres -P) but I got the message:

 pg_basebackup: directory E:\Data\Database exists but is not empty

 I creatde a tablespace using CREATE TABLESPACE at the location mentioned
 in the message. According to what I read online about this, this message
 is issued when a tablespace was created under PGDATA. In my case, only
 the directory junction pointing to my tablespace (on a different drive
 than PGDATA) exists under PGDATA, not the tablespace itself.

 The only way I can run pg_basebackup with WAL files is with option -Ft
 and -X fetch. I'd much prefer using plain mode since my 670 GB
 tablespace takes a lot of time to extract when tarred. Is there another
 way to approach this?


 All I can come up with at the moment

 So what is the path on the original machine and can it be replicated on
 the new machine, at least temporarily?


The path on the original (i.e. source) machine is: E:\Data\Database\
PG_9.3_201306121\..


 I'm thinking if the path can be replicated, let pg_basebackup write to it
 and then create the tablespace you want and do ALTER TABLE SET TABLESPACE
 to move the tables. You would also need to do this for indexes.


Not sure I understand when you say let pg_basebackup write to it. This
tablespace already exists on the source machine so cannot be written over.
It needs to be written in the backup so that I can than recreate it on the
destination machine.

Anyway, in the end I want to move the database that's in that tablespace
back to pg_default. I see two possibilities:

1) Moving it now, before taking the base backup, using ALTER DATABASE mydb
SET TABLESPACE pg_default; Then I assume I should be able to use -X stream
and plain format with pg_basebackup.

Or

2) Delete the symbolic link in data/pg_tblspc, use pg_basebackup with -X
stream and plain format, copy the tablespace from the source to the
destination machine. Create a new symbolic link in data/pg_tblspc on the
new machine and point it to the copied tablespace.

Are these two approaches feasible?





 Thanks.




 --
 Adrian Klaver
 adrian.kla...@aklaver.com

Re: [GENERAL] Starting new cluster from base backup

[Guillaume Drolet]

Adrian: thanks for this information.

I tried running pg_basebackup in plain format with option -X stream
(pg_basebackup -D F:\208376PT\db -X stream -l 208376PT17022015 -U
postgres -P) but I got the message:

pg_basebackup: directory E:\Data\Database exists but is not empty

I creatde a tablespace using CREATE TABLESPACE at the location mentioned in
the message. According to what I read online about this, this message is
issued when a tablespace was created under PGDATA. In my case, only the
directory junction pointing to my tablespace (on a different drive than
PGDATA) exists under PGDATA, not the tablespace itself.

The only way I can run pg_basebackup with WAL files is with option -Ft and
-X fetch. I'd much prefer using plain mode since my 670 GB tablespace takes
a lot of time to extract when tarred. Is there another way to approach
this?

Thanks.



2015-02-16 15:21 GMT-05:00 Adrian Klaver adrian.kla...@aklaver.com:

 On 02/16/2015 11:31 AM, Guillaume Drolet wrote:

 Dear listers,

 I want to move a cluster from one machine to another. I used
 pg_basebackup to create an archive and copied/extracted it over the old
 PGDATA location on the new machine (the server was stopped). If I start
 pgsql I get these messages in my log file:

 2015-02-16 14:29:12 EST LOG:  database system was interrupted; last
 known up at 2015-02-07 06:31:41 EST
 2015-02-16 14:29:12 EST LOG:  invalid checkpoint record
 2015-02-16 14:29:12 EST FATAL:  could not locate required checkpoint
 record
 2015-02-16 14:29:12 EST HINT:  If you are not restoring from a backup,
 try removing the file E:/data/backup_label.
 2015-02-16 14:29:12 EST LOG:  startup process (PID 3148) exited with
 exit code 1
 2015-02-16 14:29:12 EST LOG:  aborting startup due to startup process
 failure

 I assume this is due to the fact the pg_xlog folder is empty (this is
 how pg_basebackup makes it in the archive) and that I haven't supplied a
 recovery.conf file with the restore restore_command = 'copy
 E:\\archivedir\\%f %p', and the archived WAL files.

 Now my question is: it this a correct way of moving a cluster between
 machines?

 If yes, what WAL files will I put in pg_xlog? Would I have needed to
 copy those that were in the old machine right after the base backup?

 If this is not the right way to do it, what is the best way?


 http://www.postgresql.org/docs/9.3/interactive/app-pgbasebackup.html

 
 -X method
 --xlog-method=method

 Includes the required transaction log files (WAL files) in the backup.
 This will include all transaction logs generated during the backup. If this
 option is specified, it is possible to start a postmaster directly in the
 extracted directory without the need to consult the log archive, thus
 making this a completely standalone backup 
 

 There is more under -X, so I would read the whole section.


 Thanks a lot for your help,

 Guillaume




 --
 Adrian Klaver
 adrian.kla...@aklaver.com

Re: [GENERAL] Starting new cluster from base backup

[Guillaume Drolet]

This provides part of the answer to my previous post, from the 9.4 doc
(although I'm running 9.3 but I guess the second phrase in the paragraph
applies to my case):

Tablespaces will in plain format by default be backed up to the same path
they have on the server, unless the option --tablespace-mapping is used.
Without this option, running a plain format base backup on the same host as
the server will not work if tablespaces are in use, because the backup
would have to be written to the same directory locations as the original
tablespaces.

I know the -T option is not available in 9.3. Is there another way to
circumvent the problem and still be able to backup using -X stream and
plain format when tablespace have been created elsewhere?

Thanks!

2015-02-17 9:54 GMT-05:00 Guillaume Drolet droletguilla...@gmail.com:

 Adrian: thanks for this information.

 I tried running pg_basebackup in plain format with option -X stream
 (pg_basebackup -D F:\208376PT\db -X stream -l 208376PT17022015 -U
 postgres -P) but I got the message:

 pg_basebackup: directory E:\Data\Database exists but is not empty

 I creatde a tablespace using CREATE TABLESPACE at the location mentioned
 in the message. According to what I read online about this, this message is
 issued when a tablespace was created under PGDATA. In my case, only the
 directory junction pointing to my tablespace (on a different drive than
 PGDATA) exists under PGDATA, not the tablespace itself.

 The only way I can run pg_basebackup with WAL files is with option -Ft and
 -X fetch. I'd much prefer using plain mode since my 670 GB tablespace takes
 a lot of time to extract when tarred. Is there another way to approach
 this?

 Thanks.



 2015-02-16 15:21 GMT-05:00 Adrian Klaver adrian.kla...@aklaver.com:

 On 02/16/2015 11:31 AM, Guillaume Drolet wrote:

 Dear listers,

 I want to move a cluster from one machine to another. I used
 pg_basebackup to create an archive and copied/extracted it over the old
 PGDATA location on the new machine (the server was stopped). If I start
 pgsql I get these messages in my log file:

 2015-02-16 14:29:12 EST LOG:  database system was interrupted; last
 known up at 2015-02-07 06:31:41 EST
 2015-02-16 14:29:12 EST LOG:  invalid checkpoint record
 2015-02-16 14:29:12 EST FATAL:  could not locate required checkpoint
 record
 2015-02-16 14:29:12 EST HINT:  If you are not restoring from a backup,
 try removing the file E:/data/backup_label.
 2015-02-16 14:29:12 EST LOG:  startup process (PID 3148) exited with
 exit code 1
 2015-02-16 14:29:12 EST LOG:  aborting startup due to startup process
 failure

 I assume this is due to the fact the pg_xlog folder is empty (this is
 how pg_basebackup makes it in the archive) and that I haven't supplied a
 recovery.conf file with the restore restore_command = 'copy
 E:\\archivedir\\%f %p', and the archived WAL files.

 Now my question is: it this a correct way of moving a cluster between
 machines?

 If yes, what WAL files will I put in pg_xlog? Would I have needed to
 copy those that were in the old machine right after the base backup?

 If this is not the right way to do it, what is the best way?


 http://www.postgresql.org/docs/9.3/interactive/app-pgbasebackup.html

 
 -X method
 --xlog-method=method

 Includes the required transaction log files (WAL files) in the
 backup. This will include all transaction logs generated during the backup.
 If this option is specified, it is possible to start a postmaster directly
 in the extracted directory without the need to consult the log archive,
 thus making this a completely standalone backup 
 

 There is more under -X, so I would read the whole section.


 Thanks a lot for your help,

 Guillaume




 --
 Adrian Klaver
 adrian.kla...@aklaver.com

[GENERAL] Starting new cluster from base backup

[Guillaume Drolet]

Dear listers,

I want to move a cluster from one machine to another. I used pg_basebackup
to create an archive and copied/extracted it over the old PGDATA location
on the new machine (the server was stopped). If I start pgsql I get these
messages in my log file:

2015-02-16 14:29:12 EST LOG:  database system was interrupted; last known
up at 2015-02-07 06:31:41 EST
2015-02-16 14:29:12 EST LOG:  invalid checkpoint record
2015-02-16 14:29:12 EST FATAL:  could not locate required checkpoint record
2015-02-16 14:29:12 EST HINT:  If you are not restoring from a backup, try
removing the file E:/data/backup_label.
2015-02-16 14:29:12 EST LOG:  startup process (PID 3148) exited with exit
code 1
2015-02-16 14:29:12 EST LOG:  aborting startup due to startup process
failure

I assume this is due to the fact the pg_xlog folder is empty (this is how
pg_basebackup makes it in the archive) and that I haven't supplied a
recovery.conf file with the restore restore_command = 'copy
E:\\archivedir\\%f %p', and the archived WAL files.

Now my question is: it this a correct way of moving a cluster between
machines?

If yes, what WAL files will I put in pg_xlog? Would I have needed to copy
those that were in the old machine right after the base backup?

If this is not the right way to do it, what is the best way?

Thanks a lot for your help,

Guillaume

Re: [GENERAL] Cluster seems broken after pg_basebackup

[Guillaume Drolet]

Adrian, in response to your question:


 2015-02-06 07:11:38 EST FATAL:  le rôle « 208375PT$ » n'existe pas


So where is role 208375PT$ supposed to come from?

I found that when I stop/start/restart pgsql through the services.msc
application in Windows, this message is issued in the log file. This makes
sense since the account I use to start the services.msc application is my
admin account, 208375PT\Admlocal.

If I use instead the command line with pg_ctl restart -U postgres, I
don't get this message in the log file.


2015-02-06 11:28 GMT-05:00 Adrian Klaver adrian.kla...@aklaver.com:

 On 02/06/2015 05:03 AM, Guillaume Drolet wrote:

 Hi,

 Yesterday I ran a pg_basebackup of my cluster. Since it has completed,
 my cluster doesn't work properly. I tried restarting the computer (or
 service) a few times but I always get the same messages in my logs (it's
 in French. If someone is willing to help me I can try to translate the
 logs. Just ask):


 Enter Google Translate:)

 First some questions:

 1) What Postgres version?

 2) What OS(s)? I am assuming Windows from the log info below, but we all
 know what assuming gets you.

 3) Where were you backing up from and to?

 4) Which cluster does not start, the master or the child you created with
 pg_basebackup?


 2015-02-06 07:11:38 EST LOG:  le système de bases de données a été
 interrompu ; dernier lancement connu à 2015-02-06 07:05:05 EST
 2015-02-06 07:11:38 EST LOG:  le système de bases de données n'a pas été
 arrêté proprement ; restauration
  automatique en cours
 2015-02-06 07:11:38 EST LOG:  record with zero length at 24B/2C000160
 2015-02-06 07:11:38 EST LOG:  la ré-exécution n'est pas nécessaire
 2015-02-06 07:11:38 EST LOG:  le système de bases de données est prêt
 pour accepter les connexions
 2015-02-06 07:11:38 EST LOG:  lancement du processus autovacuum
 2015-02-06 07:11:38 EST FATAL:  le rôle « 208375PT$ » n'existe pas


 So where is role 208375PT$ supposed to come from?


 Then if I start pgAdmin I get a series of pop-ups I have to click OK to
 to continue:

 An error has ocurred: Column not found in pgSet: datlastsysoid
 An error has ocurred: Column not found in pgSet: datlastsysoid
 An error has ocurred: Column not found in pgSet: oid
 An error has ocurred: Column not found in pgSet: encoding
 An error has ocurred: Column not found in pgSet: Connection to database
 broken


 Not sure about that this, someone more versed in pgAdmin will have to
 answer.


 And after that, I went back to the log file and there's new information
 added:

 2015-02-06 07:51:05 EST LOG:  processus serveur (PID 184) a été arrêté
 par l'exception 0x8004
 2015-02-06 07:51:05 EST DÉTAIL:  Le processus qui a échoué exécutait :
 SELECT version();
 2015-02-06 07:51:05 EST ASTUCE :  Voir le fichier d'en-tête C «
 ntstatus.h » pour une description de la valeur
  hexadécimale.


 Well according to here:

 https://msdn.microsoft.com/en-us/library/cc704588.aspx

 0x8004
 STATUS_SINGLE_STEP


 {EXCEPTION} Single Step A single step or trace operation has just been
 completed.

 A developer is going to have explain what that means.


  2015-02-06 07:51:05 EST LOG:  arrêt des autres processus serveur actifs
 2015-02-06 07:51:05 EST ATTENTION:  arrêt de la connexion à cause de
 l'arrêt brutal d'un autre processus serveur
 2015-02-06 07:51:05 EST DÉTAIL:  Le postmaster a commandé à ce processus
 serveur d'annuler la transaction
  courante et de quitter car un autre processus serveur a quitté
 anormalement
  et qu'il existe probablement de la mémoire partagée corrompue.
 2015-02-06 07:51:05 EST ASTUCE :  Dans un moment, vous devriez être
 capable de vous reconnecter à la base de
  données et de relancer votre commande.
 2015-02-06 07:51:05 EST LOG:  processus d'archivage (PID 692) quitte
 avec le code de sortie 1
 2015-02-06 07:51:05 EST LOG:  tous les processus serveur se sont
 arrêtés, réinitialisation
 2015-02-06 07:51:15 EST FATAL:  le bloc de mémoire partagé pré-existant
 est toujours en cours d'utilisation
 2015-02-06 07:51:15 EST ASTUCE :  Vérifier s'il n'y a pas de vieux
 processus serveur en cours d'exécution. Si c'est le
  cas, fermez-les.

 I was about to try restarting postgresql using the base backup I made
 yesterday but since this means I'll have to copy my database again (700
 GB takes a while...) I am looking for a better solution from more
 experienced people.



 My suspicion is you copied at least partly over a running server.



 Thanks a lot for helping! Guillaume



 --
 Adrian Klaver
 adrian.kla...@aklaver.com

Re: [GENERAL] Cluster seems broken after pg_basebackup

[Guillaume Drolet]

2015-02-07 1:24 GMT-05:00 Guillaume Lelarge guilla...@lelarge.info:

 Le 6 févr. 2015 17:31, Adrian Klaver adrian.kla...@aklaver.com a
 écrit :
 
  On 02/06/2015 05:03 AM, Guillaume Drolet wrote:
 
  Hi,
 
  Yesterday I ran a pg_basebackup of my cluster. Since it has completed,
  my cluster doesn't work properly. I tried restarting the computer (or
  service) a few times but I always get the same messages in my logs (it's
  in French. If someone is willing to help me I can try to translate the
  logs. Just ask):
 
 
  Enter Google Translate:)
 

 But first, Guillaume, do yourself and everyone else a favor: turn the dam
 log into English. Set lc_messages to 'C' in postgresql.conf.

Thanks for this! I didn't know about this great feature.


  First some questions:
 
  1) What Postgres version?
 
  2) What OS(s)? I am assuming Windows from the log info below, but we all
 know what assuming gets you.
 
  3) Where were you backing up from and to?
 
  4) Which cluster does not start, the master or the child you created
 with pg_basebackup?
 
 
 
  2015-02-06 07:11:38 EST LOG:  le système de bases de données a été
  interrompu ; dernier lancement connu à 2015-02-06 07:05:05 EST
  2015-02-06 07:11:38 EST LOG:  le système de bases de données n'a pas été
  arrêté proprement ; restauration
   automatique en cours
  2015-02-06 07:11:38 EST LOG:  record with zero length at 24B/2C000160
  2015-02-06 07:11:38 EST LOG:  la ré-exécution n'est pas nécessaire
  2015-02-06 07:11:38 EST LOG:  le système de bases de données est prêt
  pour accepter les connexions
  2015-02-06 07:11:38 EST LOG:  lancement du processus autovacuum
  2015-02-06 07:11:38 EST FATAL:  le rôle « 208375PT$ » n'existe pas
 
 
  So where is role 208375PT$ supposed to come from?
 
 
 
  Then if I start pgAdmin I get a series of pop-ups I have to click OK to
  to continue:
 
  An error has ocurred: Column not found in pgSet: datlastsysoid
  An error has ocurred: Column not found in pgSet: datlastsysoid
  An error has ocurred: Column not found in pgSet: oid
  An error has ocurred: Column not found in pgSet: encoding
  An error has ocurred: Column not found in pgSet: Connection to database
  broken
 
 
  Not sure about that this, someone more versed in pgAdmin will have to
 answer.
 

 Usually you see these messages when you're using a pgadmin major release
 older than a PostgreSQL make release. For a 9.3 release, that would mean a
 pgadmin older than 1.18.


I'm running pgadmin 1.18.1

 
 
  And after that, I went back to the log file and there's new information
  added:
 
  2015-02-06 07:51:05 EST LOG:  processus serveur (PID 184) a été arrêté
  par l'exception 0x8004
  2015-02-06 07:51:05 EST DÉTAIL:  Le processus qui a échoué exécutait :
  SELECT version();
  2015-02-06 07:51:05 EST ASTUCE :  Voir le fichier d'en-tête C «
  ntstatus.h » pour une description de la valeur
   hexadécimale.
 
 
  Well according to here:
 
  https://msdn.microsoft.com/en-us/library/cc704588.aspx
 
  0x8004
  STATUS_SINGLE_STEP
 
 
  {EXCEPTION} Single Step A single step or trace operation has just been
 completed.
 
  A developer is going to have explain what that means.
 
 
 
  2015-02-06 07:51:05 EST LOG:  arrêt des autres processus serveur actifs
  2015-02-06 07:51:05 EST ATTENTION:  arrêt de la connexion à cause de
  l'arrêt brutal d'un autre processus serveur
  2015-02-06 07:51:05 EST DÉTAIL:  Le postmaster a commandé à ce processus
  serveur d'annuler la transaction
   courante et de quitter car un autre processus serveur a quitté
  anormalement
   et qu'il existe probablement de la mémoire partagée corrompue.
  2015-02-06 07:51:05 EST ASTUCE :  Dans un moment, vous devriez être
  capable de vous reconnecter à la base de
   données et de relancer votre commande.
  2015-02-06 07:51:05 EST LOG:  processus d'archivage (PID 692) quitte
  avec le code de sortie 1
  2015-02-06 07:51:05 EST LOG:  tous les processus serveur se sont
  arrêtés, réinitialisation
  2015-02-06 07:51:15 EST FATAL:  le bloc de mémoire partagé pré-existant
  est toujours en cours d'utilisation
  2015-02-06 07:51:15 EST ASTUCE :  Vérifier s'il n'y a pas de vieux
  processus serveur en cours d'exécution. Si c'est le
   cas, fermez-les.
 
  I was about to try restarting postgresql using the base backup I made
  yesterday but since this means I'll have to copy my database again (700
  GB takes a while...) I am looking for a better solution from more
  experienced people.
 
 
 
  My suspicion is you copied at least partly over a running server.
 

Re: [GENERAL] Cluster seems broken after pg_basebackup

[Guillaume Drolet]

I tried starting the cluster again. Once again everything looked fine at
the start (first three lines of this log, in English this time):

2015-02-09 11:40:55 EST LOG:  database system was shut down at 2015-02-06
09:50:21 EST
2015-02-09 11:40:55 EST LOG:  database system is ready to accept connections
2015-02-09 11:40:55 EST LOG:  autovacuum launcher started

Since it seemed to work, I opened the terminal and tried connecting to the
database:

C:\Users\admlocalpsql -U postgres -d mortalite

So far so good, I got a connection:

psql (9.3.5)
Attention : l'encodage console (850) diffère de l'encodage Windows (1252).
Les caractères 8 bits peuvent ne pas fonctionner correctement.
Voir la section « Notes aux utilisateurs de Windows » de la page
référence de psql pour les détails.
Saisissez « help » pour l'aide.

mortalite=#

I tried the help command and it worked:

mortalite=# help
Vous utilisez psql, l'interface en ligne de commande de PostgreSQL.
Saisissez:
\copyright pour les termes de distribution
\h pour l'aide-mémoire des commandes SQL
\? pour l'aide-mémoire des commandes psql
\g ou point-virgule en fin d'instruction pour exécuter la requête
\q pour quitter


But then when I tried to query the db, it crashed:

mortalite=# \dt
la connexion au serveur a été coupée de façon inattendue
Le serveur s'est peut-être arrêté anormalement avant ou durant le
traitement de la requête.
La connexion au serveur a été perdue. Tentative de réinitialisation : Échec.
!

And here's the rest of the log file after the crash:

2015-02-09 12:29:19 EST LOG:  server process (PID 2240) was terminated by
exception 0xC005
2015-02-09 12:29:19 EST DETAIL:  Failed process was running: SELECT
n.nspname as Schema,
  c.relname as Name,
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm'
THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence'
WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' END as Type,
  pg_catalog.pg_get_userbyid(c.relowner) as Owner
FROM pg_catalog.pg_class c
 LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','')
  AND n.nspname  'pg_catalog'
  AND n.nspname  'information_schema'
  AND n.nspname !~ '^pg_toast'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
2015-02-09 12:29:19 EST HINT:  See C include file ntstatus.h for a
description of the hexadecimal value.
2015-02-09 12:29:19 EST LOG:  terminating any other active server processes
2015-02-09 12:29:19 EST WARNING:  terminating connection because of crash
of another server process
2015-02-09 12:29:19 EST DETAIL:  The postmaster has commanded this server
process to roll back the current transaction and exit, because another
server process exited abnormally and possibly corrupted shared memory.
2015-02-09 12:29:19 EST HINT:  In a moment you should be able to reconnect
to the database and repeat your command.
2015-02-09 12:29:19 EST LOG:  archiver process (PID 4576) exited with exit
code 1
2015-02-09 12:29:19 EST LOG:  all server processes terminated;
reinitializing
2015-02-09 12:29:29 EST FATAL:  pre-existing shared memory block is still
in use
2015-02-09 12:29:29 EST HINT:  Check if there are any old server processes
still running, and terminate them.

According to this page
https://msdn.microsoft.com/en-ca/library/cc704588.aspx, exception
0xC005 means STATUS_ACCESS_VIOLATION - The instruction at 0x%08lx
referenced memory at 0x%08lx. The memory could not be %s. This is not of
much help to me.

I hope these additional bits of information can help someone figuring out a
solution to get my cluster up and running again.

PS. I was thinking of reinstalling PGSQL over my current install but
keeping my PGDATA. I've done it in the past for fixing problems with
starting the service and it worked. What do you think?

Cheers,

Guillaume





2015-02-09 11:37 GMT-05:00 Guillaume Drolet droletguilla...@gmail.com:



 2015-02-07 1:24 GMT-05:00 Guillaume Lelarge guilla...@lelarge.info:

 Le 6 févr. 2015 17:31, Adrian Klaver adrian.kla...@aklaver.com a
 écrit :
 
  On 02/06/2015 05:03 AM, Guillaume Drolet wrote:
 
  Hi,
 
  Yesterday I ran a pg_basebackup of my cluster. Since it has completed,
  my cluster doesn't work properly. I tried restarting the computer (or
  service) a few times but I always get the same messages in my logs
 (it's
  in French. If someone is willing to help me I can try to translate the
  logs. Just ask):
 
 
  Enter Google Translate:)
 

 But first, Guillaume, do yourself and everyone else a favor: turn the dam
 log into English. Set lc_messages to 'C' in postgresql.conf.

 Thanks for this! I didn't know about this great feature.


  First some questions:
 
  1) What Postgres version?
 
  2) What OS(s)? I am assuming Windows from the log info below, but we
 all know what assuming gets you.
 
  3) Where were you backing up from and to?
 
  4

Re: [GENERAL] Cluster seems broken after pg_basebackup

[Guillaume Drolet]

Guillaume: the cluster I try to start is the one used with pg_basebackup,
not the result of the backup.

2015-02-09 12:51 GMT-05:00 Guillaume Lelarge guilla...@lelarge.info:

 2015-02-09 18:40 GMT+01:00 Guillaume Drolet droletguilla...@gmail.com:

 I tried starting the cluster again. Once again everything looked fine at
 the start (first three lines of this log, in English this time):

 2015-02-09 11:40:55 EST LOG:  database system was shut down at 2015-02-06
 09:50:21 EST
 2015-02-09 11:40:55 EST LOG:  database system is ready to accept
 connections
 2015-02-09 11:40:55 EST LOG:  autovacuum launcher started

 Since it seemed to work, I opened the terminal and tried connecting to
 the database:

 C:\Users\admlocalpsql -U postgres -d mortalite

 So far so good, I got a connection:

 psql (9.3.5)
 Attention : l'encodage console (850) diffère de l'encodage Windows (1252).
 Les caractères 8 bits peuvent ne pas fonctionner correctement.
 Voir la section « Notes aux utilisateurs de Windows » de la
 page
 référence de psql pour les détails.
 Saisissez « help » pour l'aide.

 mortalite=#

 I tried the help command and it worked:

 mortalite=# help
 Vous utilisez psql, l'interface en ligne de commande de PostgreSQL.
 Saisissez:
 \copyright pour les termes de distribution
 \h pour l'aide-mémoire des commandes SQL
 \? pour l'aide-mémoire des commandes psql
 \g ou point-virgule en fin d'instruction pour exécuter la requête
 \q pour quitter


 But then when I tried to query the db, it crashed:

 mortalite=# \dt
 la connexion au serveur a été coupée de façon inattendue
 Le serveur s'est peut-être arrêté anormalement avant ou durant le
 traitement de la requête.
 La connexion au serveur a été perdue. Tentative de réinitialisation :
 Échec.
 !

 And here's the rest of the log file after the crash:

 2015-02-09 12:29:19 EST LOG:  server process (PID 2240) was terminated by
 exception 0xC005
 2015-02-09 12:29:19 EST DETAIL:  Failed process was running: SELECT
 n.nspname as Schema,
   c.relname as Name,
   CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm'
 THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence'
 WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' END as Type,
   pg_catalog.pg_get_userbyid(c.relowner) as Owner
 FROM pg_catalog.pg_class c
  LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
 WHERE c.relkind IN ('r','')
   AND n.nspname  'pg_catalog'
   AND n.nspname  'information_schema'
   AND n.nspname !~ '^pg_toast'
   AND pg_catalog.pg_table_is_visible(c.oid)
 ORDER BY 1,2;
 2015-02-09 12:29:19 EST HINT:  See C include file ntstatus.h for a
 description of the hexadecimal value.
 2015-02-09 12:29:19 EST LOG:  terminating any other active server
 processes
 2015-02-09 12:29:19 EST WARNING:  terminating connection because of crash
 of another server process
 2015-02-09 12:29:19 EST DETAIL:  The postmaster has commanded this server
 process to roll back the current transaction and exit, because another
 server process exited abnormally and possibly corrupted shared memory.
 2015-02-09 12:29:19 EST HINT:  In a moment you should be able to
 reconnect to the database and repeat your command.
 2015-02-09 12:29:19 EST LOG:  archiver process (PID 4576) exited with
 exit code 1
 2015-02-09 12:29:19 EST LOG:  all server processes terminated;
 reinitializing
 2015-02-09 12:29:29 EST FATAL:  pre-existing shared memory block is still
 in use
 2015-02-09 12:29:29 EST HINT:  Check if there are any old server
 processes still running, and terminate them.

 According to this page
 https://msdn.microsoft.com/en-ca/library/cc704588.aspx, exception
 0xC005 means STATUS_ACCESS_VIOLATION - The instruction at 0x%08lx
 referenced memory at 0x%08lx. The memory could not be %s. This is not of
 much help to me.

 I hope these additional bits of information can help someone figuring out
 a solution to get my cluster up and running again.

 PS. I was thinking of reinstalling PGSQL over my current install but
 keeping my PGDATA. I've done it in the past for fixing problems with
 starting the service and it worked. What do you think?


 The SQL query you see in your log is the result of your \dt. It should
 work. I don't see why reinstalling PostgreSQL will fix anything here.

 You said you did a pg_basebackup. The cluster you try to start is the
 cluster used with pg_basebackup or a restore done with the pg_basebackup?

 Cheers,

 Guillaume





 2015-02-09 11:37 GMT-05:00 Guillaume Drolet droletguilla...@gmail.com:



 2015-02-07 1:24 GMT-05:00 Guillaume Lelarge guilla...@lelarge.info:

 Le 6 févr. 2015 17:31, Adrian Klaver adrian.kla...@aklaver.com a
 écrit :
 
  On 02/06/2015 05:03 AM, Guillaume Drolet wrote:
 
  Hi,
 
  Yesterday I ran a pg_basebackup of my cluster. Since it has
 completed,
  my cluster doesn't work properly. I tried restarting

Re: [GENERAL] Cluster seems broken after pg_basebackup

[Guillaume Drolet]

2015-02-09 16:10 GMT-05:00 Jim Nasby jim.na...@bluetreble.com:

 On 2/9/15 11:51 AM, Guillaume Lelarge wrote:

 According to this page
 https://msdn.microsoft.com/en-ca/library/cc704588.aspx, exception
 0xC005 means STATUS_ACCESS_VIOLATION - The instruction at 0x%08lx
 referenced memory at 0x%08lx. The memory could not be %s. This is not of
 much help to me.


 In my experience that means that your data is corrupted.


That wouldn't be too dramatic since I can start over with another base
backup. It just takes several hours... :(

What I want to achieve in the end is to transfer my production database
from one machine to another. So here's how I did before things started to
go bad:

1. On the source machine, I took a base backup using pg_basebackup
2. I installed PGSQL on the destination machine
3. I copied the backup and extracted it on the destination machine
4. I created a PGDATA environment variable pointing to 'data' from the
extracted backup. In this backup, most of my data was in a created
tablespace so I updated the junction link (in data­\pg_tblspc) to reflect
the location of the tablespace on the new machine (It seems like when using
pg_basebackup, junction links are not saved as junction links but as empty
directories).
5. I verified I could connect to my database using psql or pgadmin. It
worked.
6. I wanted to move my tablespace to pg_default so I ran ALTER DATABASE
mydb SET TABLESPACE pg_default;
7. Ran pg_basebackup on my cluster
8. Tried to connect to database, no success...

It it the right way to proceed for this kind of operation?

Thanks!



  I hope these additional bits of information can help someone figuring
 out a solution to get my cluster up and running again.

 PS. I was thinking of reinstalling PGSQL over my current install but
 keeping my PGDATA. I've done it in the past for fixing problems with
 starting the service and it worked. What do you think?


 You could try it, but as Guillaume Drolet mentioned I don't see this
 helping.

 Since this is happening on your original database, I suspect that's what's
 been corrupted. In my experience, this means you either have faulty
 hardware, or there's a misconfiguration that means fsync isn't doing what
 it's supposed to do.
 --
 Jim Nasby, Data Architect, Blue Treble Consulting
 Data in Trouble? Get it in Treble! http://BlueTreble.com

Re: [GENERAL] Cluster seems broken after pg_basebackup

[Guillaume Drolet]

Dear Adrian,

Thanks for helping me. Sorry for the lack of details, I had said to myself
I had to not forget to give these details but I hit the send button too
fast. You know how it is...

I added more info in your reply below.


2015-02-06 11:28 GMT-05:00 Adrian Klaver adrian.kla...@aklaver.com:

 On 02/06/2015 05:03 AM, Guillaume Drolet wrote:

 Hi,

 Yesterday I ran a pg_basebackup of my cluster. Since it has completed,
 my cluster doesn't work properly. I tried restarting the computer (or
 service) a few times but I always get the same messages in my logs (it's
 in French. If someone is willing to help me I can try to translate the
 logs. Just ask):


 Enter Google Translate:)


Not a big fan. I've seen bad misunderstandings happen there!


 First some questions:

 1) What Postgres version?


9.3


 2) What OS(s)? I am assuming Windows from the log info below, but we all
 know what assuming gets you.


Windows 7


 3) Where were you backing up from and to?


Backing up from my only cluster (PGDATA) on disk E, to a backup directory
on an other disk (F:) using this command:

pg_basebackup -D F:\\db_base_backup -Fp -Xs  -R -P
--label=basebackup20150205 --username=postgres

What's weird is that I did some successful tests last week on the same
system (backing up, archiving, recovering) using the same procedure. Only
difference was the cluster, which was much smaller for testing purposes,
but located at the same place (i.e. E:\data) and PostgresSQL installed in
C:\Programs\...




 4) Which cluster does not start, the master or the child you created with
 pg_basebackup?



The master. I haven't tried the child yet. But I saw that the message about
role 208375PT$ is in logs from before the backup too.



 2015-02-06 07:11:38 EST LOG:  le système de bases de données a été
 interrompu ; dernier lancement connu à 2015-02-06 07:05:05 EST
 2015-02-06 07:11:38 EST LOG:  le système de bases de données n'a pas été
 arrêté proprement ; restauration
  automatique en cours
 2015-02-06 07:11:38 EST LOG:  record with zero length at 24B/2C000160
 2015-02-06 07:11:38 EST LOG:  la ré-exécution n'est pas nécessaire
 2015-02-06 07:11:38 EST LOG:  le système de bases de données est prêt
 pour accepter les connexions
 2015-02-06 07:11:38 EST LOG:  lancement du processus autovacuum
 2015-02-06 07:11:38 EST FATAL:  le rôle « 208375PT$ » n'existe pas


 So where is role 208375PT$ supposed to come from?


This is the local domain of my machine. I log onto my machine with a local
admin account and using domain name 208375PT (I didn't set this part of my
machine, the IT guys here at work did). The thing is: I don't understand
why it's there in the log file??




 Then if I start pgAdmin I get a series of pop-ups I have to click OK to
 to continue:

 An error has ocurred: Column not found in pgSet: datlastsysoid
 An error has ocurred: Column not found in pgSet: datlastsysoid
 An error has ocurred: Column not found in pgSet: oid
 An error has ocurred: Column not found in pgSet: encoding
 An error has ocurred: Column not found in pgSet: Connection to database
 broken


 Not sure about that this, someone more versed in pgAdmin will have to
 answer.


 And after that, I went back to the log file and there's new information
 added:

 2015-02-06 07:51:05 EST LOG:  processus serveur (PID 184) a été arrêté
 par l'exception 0x8004
 2015-02-06 07:51:05 EST DÉTAIL:  Le processus qui a échoué exécutait :
 SELECT version();
 2015-02-06 07:51:05 EST ASTUCE :  Voir le fichier d'en-tête C «
 ntstatus.h » pour une description de la valeur
  hexadécimale.


 Well according to here:

 https://msdn.microsoft.com/en-us/library/cc704588.aspx

 0x8004
 STATUS_SINGLE_STEP


 {EXCEPTION} Single Step A single step or trace operation has just been
 completed.

 A developer is going to have explain what that means.


  2015-02-06 07:51:05 EST LOG:  arrêt des autres processus serveur actifs
 2015-02-06 07:51:05 EST ATTENTION:  arrêt de la connexion à cause de
 l'arrêt brutal d'un autre processus serveur
 2015-02-06 07:51:05 EST DÉTAIL:  Le postmaster a commandé à ce processus
 serveur d'annuler la transaction
  courante et de quitter car un autre processus serveur a quitté
 anormalement
  et qu'il existe probablement de la mémoire partagée corrompue.
 2015-02-06 07:51:05 EST ASTUCE :  Dans un moment, vous devriez être
 capable de vous reconnecter à la base de
  données et de relancer votre commande.
 2015-02-06 07:51:05 EST LOG:  processus d'archivage (PID 692) quitte
 avec le code de sortie 1
 2015-02-06 07:51:05 EST LOG:  tous les processus serveur se sont
 arrêtés, réinitialisation
 2015-02-06 07:51:15 EST FATAL:  le bloc de mémoire partagé pré-existant
 est toujours en cours d'utilisation
 2015-02-06 07:51:15 EST ASTUCE :  Vérifier s'il n'y a pas de vieux
 processus serveur en cours d'exécution. Si c'est le
  cas, fermez-les.

 I was about to try restarting postgresql using the base backup I made
 yesterday but since

[GENERAL] Cluster seems broken after pg_basebackup

[Guillaume Drolet]

Hi,

Yesterday I ran a pg_basebackup of my cluster. Since it has completed, my
cluster doesn't work properly. I tried restarting the computer (or service)
a few times but I always get the same messages in my logs (it's in French.
If someone is willing to help me I can try to translate the logs. Just ask):

2015-02-06 07:11:38 EST LOG:  le système de bases de données a été
interrompu ; dernier lancement connu à 2015-02-06 07:05:05 EST
2015-02-06 07:11:38 EST LOG:  le système de bases de données n'a pas été
arrêté proprement ; restauration
automatique en cours
2015-02-06 07:11:38 EST LOG:  record with zero length at 24B/2C000160
2015-02-06 07:11:38 EST LOG:  la ré-exécution n'est pas nécessaire
2015-02-06 07:11:38 EST LOG:  le système de bases de données est prêt pour
accepter les connexions
2015-02-06 07:11:38 EST LOG:  lancement du processus autovacuum
2015-02-06 07:11:38 EST FATAL:  le rôle « 208375PT$ » n'existe pas

Then if I start pgAdmin I get a series of pop-ups I have to click OK to to
continue:

An error has ocurred: Column not found in pgSet: datlastsysoid
An error has ocurred: Column not found in pgSet: datlastsysoid
An error has ocurred: Column not found in pgSet: oid
An error has ocurred: Column not found in pgSet: encoding
An error has ocurred: Column not found in pgSet: Connection to database
broken

And after that, I went back to the log file and there's new information
added:

2015-02-06 07:51:05 EST LOG:  processus serveur (PID 184) a été arrêté par
l'exception 0x8004
2015-02-06 07:51:05 EST DÉTAIL:  Le processus qui a échoué exécutait :
SELECT version();
2015-02-06 07:51:05 EST ASTUCE :  Voir le fichier d'en-tête C « ntstatus.h
» pour une description de la valeur
hexadécimale.
2015-02-06 07:51:05 EST LOG:  arrêt des autres processus serveur actifs
2015-02-06 07:51:05 EST ATTENTION:  arrêt de la connexion à cause de
l'arrêt brutal d'un autre processus serveur
2015-02-06 07:51:05 EST DÉTAIL:  Le postmaster a commandé à ce processus
serveur d'annuler la transaction
courante et de quitter car un autre processus serveur a quitté
anormalement
et qu'il existe probablement de la mémoire partagée corrompue.
2015-02-06 07:51:05 EST ASTUCE :  Dans un moment, vous devriez être capable
de vous reconnecter à la base de
données et de relancer votre commande.
2015-02-06 07:51:05 EST LOG:  processus d'archivage (PID 692) quitte avec
le code de sortie 1
2015-02-06 07:51:05 EST LOG:  tous les processus serveur se sont arrêtés,
réinitialisation
2015-02-06 07:51:15 EST FATAL:  le bloc de mémoire partagé pré-existant est
toujours en cours d'utilisation
2015-02-06 07:51:15 EST ASTUCE :  Vérifier s'il n'y a pas de vieux
processus serveur en cours d'exécution. Si c'est le
cas, fermez-les.

I was about to try restarting postgresql using the base backup I made
yesterday but since this means I'll have to copy my database again (700 GB
takes a while...) I am looking for a better solution from more experienced
people.

Thanks a lot for helping! Guillaume

[GENERAL] Error: could not read symbolic link pg_tblspc/940585. No such file or directory

[Guillaume Drolet]

Dear list users,

I moved a database from a tablespace I had created to pg_default using:

ALTER DATABASE mydatabase SET TABLESPACE pg_default;

After the database was fully copied to a new directory under PGDATA/base, I
deleted the symbolic link to the old tablespace using (in Windows) rmdir
PGDATA/pg_tblspc/940585. I chose to delete this now useless symlink because
of an error when running pg_basebackup (something about a non-empty
directory under the symlink).

Now, when I start pgAdmin and connect to my cluster, I get the following
pop-up message (free translation from French):

An error has occurred:

ERROR: could not read symbolic link pg_tblspc/940585: No such file or
directory.

If I click OK on the pop-up, the connection is made and everything looks
good (i.e. I can access my databases, tables, etc.). My question is: where
do I have to look to delete a reference to this obsolete symbolic
link/tablespace in pgAdmin or PGSQL?

Thanks a lot for your help,

Guillaume

Re: [GENERAL] Cluster recovery - FATAL: database files are incompatible with server. Different PG_CONTROL_VERSION for cluster and server.

[Guillaume Drolet]

Thanks for the doc on pg_resetxlog. Will try it next week when I'm
back from Easter holiday, first backuping $PGDATA.

Most of my databases are stored in a default tablespace I defined on a
different disk than that of my PG installation (and $PGDATA): could I
just reinstall PG and then re-link it with my databases on that disk?

2014-04-17 20:29 GMT-04:00 Adrian Klaver adrian.kla...@aklaver.com:
 On 04/17/2014 01:29 PM, Guillaume Drolet wrote:

 Dear list users,

 For some tests, I installed a new cluster with different parameters
 than the ones I had used a while ago to create the cluster I use for
 my day-to-day activities (let's call it my main cluster). I used
 initdb --no-locale -E UTF8 -D new_data_dir. Then I used pg_ctl -D
 new_data_dir to start my new cluster. So far so good.

 Then, I realized that I wanted to add a database superuser with the
 option -U to initdb so I decided to delete the newly created cluster.
 Instead, I mistakenly deleted the data directory of my main cluster
 (doh!), using Shift-Delete. I recovered the deleted data directory
 using Panda Recovery Tool and copied it back into its location (the
 location pointed to by $PGDATA).

 Now for the real problems: if I go in pgAdmin and try to connect to my
 cluster, I get the server not listening message. I also tried going
 to the Services and restarting postgresql but it says it is already
 stopped and if I try to start it, I get a message that it has started
 but then stopped because it was unused.

 If I try pg_ctl start, I get this message (my translation from
 French):  FATAL: database files are incompatible with server. DETAIL:
 Database cluster was initialized with a PG_CONTROL_VERSION ? 16795209
 while the server was compiled with a PG_CONTROL_VERSION ? 937. HINT:
 Looks like you need initdb.

 I really don't know what to do and would appreciate any help, if
 anything can be done to recover my databases. My PG version is 9.3.3
 on a Windows 7 64-bit OS.


 Further investigation found pg_resetxlog:

 http://www.postgresql.org/docs/9.3/static/app-pgresetxlog.html

 I have never used it, so all I can do is point to the docs. I would say that
 if you go this route make a copy of your $PGDATA in another location for
 safe keeping in the event things go wrong.




 Best regards and many thanks for your advice,

 Guillaume




 --
 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] Cluster recovery - FATAL: database files are incompatible with server. Different PG_CONTROL_VERSION for cluster and server.

[Guillaume Drolet]

No the new test cluster isn't running anymore: I deleted it right
after I deleted my main one.

Re the error messages: I was calling pg_ctl with the option -D
path_to_my_main_cluster_datadir

2014-04-18 8:44 GMT-04:00 droletguillaume droletguilla...@gmail.com:



 Sent from Samsung Mobile


  Original message 
 From: Adrian Klaver
 Date:04-17-2014 20:36 (GMT-05:00)
 To: Guillaume Drolet ,pgsql-general@postgresql.org
 Subject: Re: [GENERAL] Cluster recovery - FATAL: database files are
 incompatible with server. Different PG_CONTROL_VERSION for cluster and
 server.

 On 04/17/2014 01:29 PM, Guillaume Drolet wrote:
 Dear list users,

 For some tests, I installed a new cluster with different parameters
 than the ones I had used a while ago to create the cluster I use for
 my day-to-day activities (let's call it my main cluster). I used
 initdb --no-locale -E UTF8 -D new_data_dir. Then I used pg_ctl -D
 new_data_dir to start my new cluster. So far so good.

 Then, I realized that I wanted to add a database superuser with the
 option -U to initdb so I decided to delete the newly created cluster.
 Instead, I mistakenly deleted the data directory of my main cluster
 (doh!), using Shift-Delete. I recovered the deleted data directory
 using Panda Recovery Tool and copied it back into its location (the
 location pointed to by $PGDATA).

 Now for the real problems: if I go in pgAdmin and try to connect to my
 cluster, I get the server not listening message. I also tried going
 to the Services and restarting postgresql but it says it is already
 stopped and if I try to start it, I get a message that it has started
 but then stopped because it was unused.

 If I try pg_ctl start, I get this message (my translation from
 French):  FATAL: database files are incompatible with server. DETAIL:
 Database cluster was initialized with a PG_CONTROL_VERSION ? 16795209
 while the server was compiled with a PG_CONTROL_VERSION ? 937. HINT:
 Looks like you need initdb.


 More thoughts.

 What happened to the new cluster?

 Is it still running?

 When you got the error messages where you pointing at the old or new
 cluster?


 I really don't know what to do and would appreciate any help, if
 anything can be done to recover my databases. My PG version is 9.3.3
 on a Windows 7 64-bit OS.

 Best regards and many thanks for your advice,

 Guillaume




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

[GENERAL] Cluster recovery - FATAL: database files are incompatible with server. Different PG_CONTROL_VERSION for cluster and server.

[Guillaume Drolet]

Dear list users,

For some tests, I installed a new cluster with different parameters
than the ones I had used a while ago to create the cluster I use for
my day-to-day activities (let's call it my main cluster). I used
initdb --no-locale -E UTF8 -D new_data_dir. Then I used pg_ctl -D
new_data_dir to start my new cluster. So far so good.

Then, I realized that I wanted to add a database superuser with the
option -U to initdb so I decided to delete the newly created cluster.
Instead, I mistakenly deleted the data directory of my main cluster
(doh!), using Shift-Delete. I recovered the deleted data directory
using Panda Recovery Tool and copied it back into its location (the
location pointed to by $PGDATA).

Now for the real problems: if I go in pgAdmin and try to connect to my
cluster, I get the server not listening message. I also tried going
to the Services and restarting postgresql but it says it is already
stopped and if I try to start it, I get a message that it has started
but then stopped because it was unused.

If I try pg_ctl start, I get this message (my translation from
French):  FATAL: database files are incompatible with server. DETAIL:
Database cluster was initialized with a PG_CONTROL_VERSION ? 16795209
while the server was compiled with a PG_CONTROL_VERSION ? 937. HINT:
Looks like you need initdb.

I really don't know what to do and would appreciate any help, if
anything can be done to recover my databases. My PG version is 9.3.3
on a Windows 7 64-bit OS.

Best regards and many thanks for your advice,

Guillaume


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