Обсуждение: Kernel 2.4->2.6 upgrade results in PANIC: could not locate a valid checkpoint record

Hi, we have happily been running postgresql 7.4.x (currently 7.4.12) on a
debian linux with kernel 2.4.x for some years without any issues.
Yesterday after switching to 2.6.16 we experienced the following problem
upon pgsql startup:

LOG:  could not open file "/var/lib/pgsql/data/pg_xlog/0000006900000091"
(log file 1 05, segment 145): No such file or directory
LOG:  invalid primary checkpoint record
LOG:  could not open file "/var/lib/pgsql/data/pg_xlog/0000006900000091"
(log file 1 05, segment 145): No such file or directory
LOG:  invalid secondary checkpoint record
PANIC:  could not locate a valid checkpoint record
LOG:  startup process (PID 414) was terminated by signal 6
LOG:  aborting startup due to startup process failure

After reverting back to kernel 2.4, postgresql started up hapilly again.

Any ideas?
--
-Achilleus

Problem solved.
The sysadmins yesterday forgot to mention to me
that they didnt mount correctly all the file systems
(including the one of pg_xlog).

Sorry for the noise :-)

O Achilleus Mantzios έγραψε στις Apr 14, 2006 :

>
> Hi, we have happily been running postgresql 7.4.x (currently 7.4.12) on a
> debian linux with kernel 2.4.x for some years without any issues.
> Yesterday after switching to 2.6.16 we experienced the following problem
> upon pgsql startup:
>
> LOG:  could not open file "/var/lib/pgsql/data/pg_xlog/0000006900000091"
> (log file 1 05, segment 145): No such file or directory
> LOG:  invalid primary checkpoint record
> LOG:  could not open file "/var/lib/pgsql/data/pg_xlog/0000006900000091"
> (log file 1 05, segment 145): No such file or directory
> LOG:  invalid secondary checkpoint record
> PANIC:  could not locate a valid checkpoint record
> LOG:  startup process (PID 414) was terminated by signal 6
> LOG:  aborting startup due to startup process failure
>
> After reverting back to kernel 2.4, postgresql started up hapilly again.
>
> Any ideas?
>

--
-Achilleus

Hi, i have run into the following problem.
Let me describe the context first.

When i joined the company(ies) i work for (a group of Shipping Mgmt/
Owenship/Agent companies), the only thing i was told when i started
designing the DB/Apps was just one company.

So i built everything into one single DB, and i wrote the apps
having one company in mind.

Our architecture is based on jboss3/postgresql (curenctly 7.4.12).
There is one .ear file, which authenticates users against a lotus
notes ldap server.

At the time, the corporate organisational model was a little bit wierd:
- Many Indepentent ownership companies
- Many Independent Mgmg companies
(but all busines was with one company in mind).

Each App user is a member of one or more ldap groups, each group
mapping to a mgmt company.

So i ended up with
- one DB with 173 tables
- one DB user (postgres)
- one .EAR application with 148,827 lines of code.

Now the requirements start to change.
The boss now bought some other types of vessels too.
So virtually there must be N separate distinct apps, where N is the number
of Mgmt companies (roughly one for every type of vessel), where each app
sees and manages only its data.

Moreover there are some apps that should see global data for some specific
tables. (like the crew data, people in the crew move from one type of
vessel to the other so they are not tied to a Mgmt company).

These new requirements are of legal nature, as well as of
operational. (People managing a type of vessels dont want to mess with
another type,
and auditors must see each company completely separated from the rest).

Doing it with extra code would be a real pain, since i would have to
refine
all security/authentication based on the groups (groups@mgmt_companies)
that a person belongs to. Also this way no inherent isolation/security
would hold.

Now i am thinking of restructuring the whole architecture as:
- Create one EAR app for every mgmt company
- Create one DB USER for every mgmg company
- Create one SCHEMA (same as the USER) for every mgmt company
(mgmtcompany1,mgmtcompany2,etc...)
- Find a way (links/xdoclet/eclipse?) to have *one* common code base for
the N EAR apps.
- Tweak with jboss*.xml to map java:comp/env/jdbc/<mgmt company>DB to
java:/<mgmt company>pgsql, where <mgmt company>pgsql authenticates
with the corresponding DB USER.
- Classify the tables into
  - The ones that apply to ALL mgmt companies (and leave them in the
public schema)
  - The ones that apply *only* to a mgmt company and so create one under
each SCHEMA
- Load the data in *each* SCHEMA, except the tables that apply to all.
- Define a process of "mgmt company"fying the tables in each schema (e.g.
delete from mgmtcompany1.vessels the vessels that dont belong to
mgmtcompany1, and so forth)
- Resolve FK constraint issues
- The default search_path in psql (whats the the equivalent in jdbc?) is
$user,public, so effectively *each* EAR will hit automagically the correct
mgmtcompanyN.* tables, or the public.* tables if these tables apply to all
mgmt companies.

With this way, the hard work is DB oriented, and not APP oriented.
However i wonder whether someone else has gone thru a similar process,
or if someone finds some assumption conceptually flawed.

Thanx for reading, and for any possible thoughts.

--
-Achilleus

Hi, Achilleus,

Achilleus Mantzios wrote:

> Now i am thinking of restructuring the whole architecture as:
> - Create one EAR app for every mgmt company
> - Create one DB USER for every mgmg company
> - Create one SCHEMA (same as the USER) for every mgmt company
> (mgmtcompany1,mgmtcompany2,etc...)

We're doing a very similar thing here for one of our legacy apps, which
luckily does not know anything about schemas, and so the search_path
trick does work.

However, for most "global" tables we have views with insert/update/
delete rules in the specific schemas, and such shield the application
from directly accessing the global data. We even need to mere local and
global data this way in some cases.

It is ugly, but it works fine and is manageable.

HTH,
Markus
--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org

Hi, i have run into the following problem.
Let me describe the context first.

When i joined the company(ies) i work for (a group of Shipping Mgmt/
Owenship/Agent companies), the only thing i was told when i started
designing the DB/Apps was just one company.

So i built everything into one single DB, and i wrote the apps
having one company in mind.

Our architecture is based on jboss3/postgresql (curenctly 7.4.12).
There is one .ear file, which authenticates users against a lotus
notes ldap server.

At the time, the corporate organisational model was a little bit wierd:
- Many Indepentent ownership companies
- Many Independent Mgmg companies
(but all busines was with one company in mind).

Each App user is a member of one or more ldap groups, each group
mapping to a mgmt company.

So i ended up with
- one DB with 173 tables
- one DB user (postgres)
- one .EAR application with 148,827 lines of code.

Now the requirements start to change.
The boss now bought some other types of vessels too.
So virtually there must be N separate distinct apps, where N is the number
of Mgmt companies (roughly one for every type of vessel), where each app
sees and manages only its data.

Moreover there are some apps that should see global data for some specific
tables. (like the crew data, people in the crew move from one type of
vessel to the other so they are not tied to a Mgmt company).

These new requirements are of legal nature, as well as of
operational. (People managing a type of vessels dont want to mess with
another type,
and auditors must see each company completely separated from the rest).

Doing it with extra code would be a real pain, since i would have to
refine
all security/authentication based on the groups ( groups@mgmt_companies)
that a person belongs to. Also this way no inherent isolation/security
would hold.

Now i am thinking of restructuring the whole architecture as:
- Create one EAR app for every mgmt company
- Create one DB USER for every mgmg company
- Create one SCHEMA (same as the USER) for every mgmt company
(mgmtcompany1,mgmtcompany2,etc...)
- Find a way (links/xdoclet/eclipse?) to have *one* common code base for
the N EAR apps.
- Tweak with jboss*.xml to map java:comp/env/jdbc/<mgmt company>DB to
java:/<mgmt company>pgsql, where <mgmt company>pgsql authenticates
with the corresponding DB USER.
- Classify the tables into
- The ones that apply to ALL mgmt companies (and leave them in the
public schema)
- The ones that apply *only* to a mgmt company and so create one under
each SCHEMA
- Load the data in *each* SCHEMA, except the tables that apply to all.
- Define a process of "mgmt company"fying the tables in each schema (e.g.
delete from mgmtcompany1.vessels the vessels that dont belong to
mgmtcompany1, and so forth)
- Resolve FK constraint issues
- The default search_path in psql (whats the the equivalent in jdbc?) is
$user,public, so effectively *each* EAR will hit automagically the correct
mgmtcompanyN.* tables, or the public.* tables if these tables apply to all
mgmt companies.

With this way, the hard work is DB oriented, and not APP oriented.
However i wonder whether someone else has gone thru a similar process,
or if someone finds some assumption conceptually flawed.

Thanx for reading, and for any possible thoughts.

--
-Achilleus



---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

              http://archives.postgresql.org

O Markus Schaber έγραψε στις Apr 18, 2006 :

> Hi, Achilleus,
>
> Achilleus Mantzios wrote:
>
> > Now i am thinking of restructuring the whole architecture as:
> > - Create one EAR app for every mgmt company
> > - Create one DB USER for every mgmg company
> > - Create one SCHEMA (same as the USER) for every mgmt company
> > (mgmtcompany1,mgmtcompany2,etc...)
>
> We're doing a very similar thing here for one of our legacy apps, which
> luckily does not know anything about schemas, and so the search_path
> trick does work.
>
> However, for most "global" tables we have views with insert/update/
> delete rules in the specific schemas, and such shield the application
> from directly accessing the global data. We even need to mere local and
> global data this way in some cases.
>
> It is ugly, but it works fine and is manageable.

If no exotic/contrib code is to be used then i think
splitting into separate Schemas (versus separate DBs) will make future
consolidation/stats/accounting (global data) code easy to write.
(Unless ofcourse some real cross-db sql join features appear which is not
the case at the moment).
Why do you think its ugly after all?
>
> HTH,
> Markus
>

--
-Achilleus

O Luckys έγραψε στις Apr 18, 2006 :

> how about having a company table, and company_code column across all
> relevant table, although you'll have to modify your application also, which
> would be an additional clause in the WHERE condition e.g where company_code
> = 'which company user has logged in'.
> The user has to specify while logging under which company he's going to work
> on.
> This way would be ideal even for your Global financial consolidations if the
> mgmt requires in the due course.
> other option would be of two tables, Company , Organization, where you can
> have company1, org1, org2 etc., this can also be applied in the same pattern
> as stated above.

Thanx for your thoughts, but this would require touching
173 tables +
2,594 SQL statements in a sum of 324 programs (which sum into 125,085
lines of code)

>
>
>
> On 4/18/06, Achilleus Mantzios <achill@matrix.gatewaynet.com> wrote:
>
> >
> > Hi, i have run into the following problem.
> > Let me describe the context first.
> >
> > When i joined the company(ies) i work for (a group of Shipping Mgmt/
> > Owenship/Agent companies), the only thing i was told when i started
> > designing the DB/Apps was just one company.
> >
> > So i built everything into one single DB, and i wrote the apps
> > having one company in mind.
> >
> > Our architecture is based on jboss3/postgresql (curenctly 7.4.12).
> > There is one .ear file, which authenticates users against a lotus
> > notes ldap server.
> >
> > At the time, the corporate organisational model was a little bit wierd:
> > - Many Indepentent ownership companies
> > - Many Independent Mgmg companies
> > (but all busines was with one company in mind).
> >
> > Each App user is a member of one or more ldap groups, each group
> > mapping to a mgmt company.
> >
> > So i ended up with
> > - one DB with 173 tables
> > - one DB user (postgres)
> > - one .EAR application with 148,827 lines of code.
> >
> > Now the requirements start to change.
> > The boss now bought some other types of vessels too.
> > So virtually there must be N separate distinct apps, where N is the number
> > of Mgmt companies (roughly one for every type of vessel), where each app
> > sees and manages only its data.
> >
> > Moreover there are some apps that should see global data for some specific
> > tables. (like the crew data, people in the crew move from one type of
> > vessel to the other so they are not tied to a Mgmt company).
> >
> > These new requirements are of legal nature, as well as of
> > operational. (People managing a type of vessels dont want to mess with
> > another type,
> > and auditors must see each company completely separated from the rest).
> >
> > Doing it with extra code would be a real pain, since i would have to
> > refine
> > all security/authentication based on the groups (groups@mgmt_companies)
> > that a person belongs to. Also this way no inherent isolation/security
> > would hold.
> >
> > Now i am thinking of restructuring the whole architecture as:
> > - Create one EAR app for every mgmt company
> > - Create one DB USER for every mgmg company
> > - Create one SCHEMA (same as the USER) for every mgmt company
> > (mgmtcompany1,mgmtcompany2,etc...)
> > - Find a way (links/xdoclet/eclipse?) to have *one* common code base for
> > the N EAR apps.
> > - Tweak with jboss*.xml to map java:comp/env/jdbc/<mgmt company>DB to
> > java:/<mgmt company>pgsql, where <mgmt company>pgsql authenticates
> > with the corresponding DB USER.
> > - Classify the tables into
> > - The ones that apply to ALL mgmt companies (and leave them in the
> > public schema)
> > - The ones that apply *only* to a mgmt company and so create one under
> > each SCHEMA
> > - Load the data in *each* SCHEMA, except the tables that apply to all.
> > - Define a process of "mgmt company"fying the tables in each schema (e.g.
> > delete from mgmtcompany1.vessels the vessels that dont belong to
> > mgmtcompany1, and so forth)
> > - Resolve FK constraint issues
> > - The default search_path in psql (whats the the equivalent in jdbc?) is
> > $user,public, so effectively *each* EAR will hit automagically the correct
> > mgmtcompanyN.* tables, or the public.* tables if these tables apply to all
> > mgmt companies.
> >
> > With this way, the hard work is DB oriented, and not APP oriented.
> > However i wonder whether someone else has gone thru a similar process,
> > or if someone finds some assumption conceptually flawed.
> >
> > Thanx for reading, and for any possible thoughts.
> >
> > --
> > -Achilleus
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Have you searched our list archives?
> >
> >               http://archives.postgresql.org
> >
>
>
>
> --
> regards, Luckys...
>

--
-Achilleus

Hi, Achilleus,

Achilleus Mantzios wrote:
[schema trickery]
> Why do you think its ugly after all?

It is not ugly to split such things via schemas per se, but it is ugly
to use this schema trick together with a bunch of views, rules and
triggers to "upgrade" a legacy single-deployment application into a
multi-deployment application with some limited cooperation / data sharing.

Markus
--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org

On Tue, 2006-04-18 at 14:32 +0300, Achilleus Mantzios wrote:
> Thanx for your thoughts, but this would require touching
> 173 tables +
> 2,594 SQL statements in a sum of 324 programs (which sum into 125,085
> lines of code)


We did a very similar conversion to the one proposed here a couple of
years back, and switched an old Java app from a dedicated schema-per-
client model to a shared schema with company ID model.  I figured I'd
share our results, to at least give you a data point on what you could
expect if you went that route.

At the time, the application consisted of roughly 90 tables and 90,000
lines of code.  Never counted the SQL statements there were probably
very roughly 1000.

We had the advantage of having the 90,000 LOC spread across only two
larger applications instead of a bunch of small programs.

It actually ended up being a much easier process than we had expected,
because the application logic didn't really need change all that much.
The major steps were:

1. Add an extra company FK to the top-level objects in our data model.
For example, we added companyId to the userdetail table, but not to the
address table which depends on the userdetail table.  In our case, this
meant that we needed to add a column to about 30 (one third) of our
tables.  Not very difficult.

2. Inserts into those 30 tables were modified to include an extra
parameter.  The actual insert change is trivial, but depending on your
plumbing it might take some refactoring to get the companyId to the code
that does the inserts.  This involved a moderate code refactoring, but
the changes were very straightforward and not too prone to errors.

3. Because we didn't support moving from one company to another, the
update and most of the select logic didn't change at all.

4. Needed to update all of the search queries to only search within the
current company.  Fortunately, this type of query is relatively rare.
In our case, there were only about 20 that needed updates out of our
1000 queries.  The change is also simple, just add one AND to the WHERE
clause.

5. Security checks.  This is the only step that took a good chunk of
time, because our application is web-based and so a mischievous user
could muck with id's in the HTTP POSTS on pretty much any page in our
application.  This meant that we needed to be careful to always do
company checks before processing any user input.  Fortunately for us, at
the same time we were enhancing our security model anyway, so doing the
extra company check really added no extra time on top of the work that
needed to be done anyway.

Steps 1-4 took a single developer 3 days to implement.  Step 5 was
harder to measure because as I mentioned we bundled it together with a
larger security overhaul, but to perform it separately (and do an audit
to make sure it was correct) would have taken perhaps a week or two.

Anyway, overall in our case we had a pretty successful conversion.  It
ended taking significantly less time than anticipated, and the decreased
maintenance of only managing one database and the ease of building new
global administrative and reporting tools definitely made the project
worth while.

-- Mark Lewis