Subscribe to this thread
Home - General / All posts - Moving Enterprise Server to new hardware and postgres version
mikedufty

845 post(s)
#20-Jun-19 08:24

We have been running an Enterprise Server on Postgres 8.3.0 on FreeBSD for many years and it has been totally reliable and maintenance free.

Unfortunately its now so out of date that the virtualisation software on our new server hardware doesn't support it.

We thought we'd take the chance to upgrade to a newer version of postgres and now have postgres 10 running on ubuntu 16.04LTS.

I have transferred the database over using postgres tools (pgdump) and it appears all the data is there, but I now get the message "Can't retrieve Enterprise Server Configuration" when trying to connect to it in Server Console.

I can share a drawing to it, and it appears to work within that .map file, but can't get into server console to add to another project.

Anyone have any suggestions? I've read the help file on setting up new enterprise servers but didn't find anything on moving or restoring existing ones.

I suppose I should try setting up a new one on the current setup to see if it is just a postgres version issue, but I suspect something has changed or gone missing in the move.

Note - this is with Manifold 8 (we have 32 bit database administrator edition), postgres is 64 bit on 64 bit ubuntu if that matters.

adamw


8,775 post(s)
#20-Jun-19 08:40

Did you copy all tables? You need to copy (a) mfd_root, (b) mfdXXX_data, (c) mfdXXX_id.

Did binary data survive copying? pg_dump turns off binary values by default for some switches (eg, --table or --schema), you need to be careful to keep them during the copy.

Manifold 8 being 32-bit and PostgreSQL being 64-bit is fine.

You can always import all components shared on the old database into a MAP file, connect to the brand new database (no mfdXXX tables at all), create a new Enterprise storage, then re-share the components.

mikedufty

845 post(s)
#20-Jun-19 09:25

I'll have to check on the binary data. I remember 'blob' being selected somewhere in the export/import process which I thought sounded like it should preserve everything, but I don't really know what I am doing. Is there an easy way to check if the binary data was preserved.

We've been using Manifold since version 5 and have very many server console components saved across many manifold files and are hoping to keep those all linked and working.

They are all locally cached though, so I guess in the worst case could just re-share them as needed.

mikedufty

845 post(s)
#20-Jun-19 09:44

I thought the mfd files were there, but actually I seem to be missing some.

There are ~1000 public.mfdXXX_data

public.mfd_root is there, but I don't see any mfdXXX_id

In the working install I also don't see any mfdXXX_id either

Both have a table MFD_META which I can see in PGadmin, but not in Manifold database console or administrator console

adamw


8,775 post(s)
#20-Jun-19 13:08

mfdXXX_id may or may not exist. If it does not exist on the original database, it's fine for it not to exist on the new database.

That mfd_meta is hidden in 8 is normal. If the original database has it, the new database better has it as well with the exact same data, although it might have been created by 9 rather than 8.

mikedufty

845 post(s)
#20-Jun-19 10:04

I just tried sharing a drawing to the default postgres database on this install, and had the same result.

Drawing now has the lock and seems to function normally with checkout etc, but I get the error message trying to access server console for that database.

Are there some postgres permissions or plug ins or settings I need, or could the version (10.8) be incompatible with M8?

adamw


8,775 post(s)
#20-Jun-19 13:11

I doubt this is related to any incompatibilities. I think it is more likely that the binary values did not get copied.

If you are fine with uploading your dump (and the command line you use to restore it) to tech support (encrypting / compressing first, obviously, and sending the password), they can check what's going on in detail.

Alternatively, create a new database and move the data by importing to MAP / re-sharing to the new database.

mikedufty

845 post(s)
#20-Jun-19 17:08

as mentioned above I tried a new database and had the same error, so I think there is something wrong with what I'm doing rather than the export/import.

Just had a play around with it from home, and it seems to work from there.

I can now see all the data and link it - yay!

I get a different error from here - now when I try to share a layer to the database I get error: Relatio "mfd2880_data does not exist; error while preparing parameters."

Looks like maybe the imported roles don't have permissions to make tables on the new server?

I got the same error trying to share a table to a new blank database with the postgres user on this system though.

Going back to the work machine I still get the same error there. So I can share drawings from that machine, and read them from the home machine, but I can't read on the work machine or share from the home one!

Home computer is also 8.0.30 but enterprise edition, not database administrator. Accessing the database via a VPN. Also is Win7, work is Win10. I might have to try another machine at work.

There is something funny with the imported login roles, pgadmin4 doesn't display any permissions for them or allow them to be edited.

Just tested the home computer with the our old enterprise database, and it fails trying to share to that too.

adamw


8,775 post(s)
#21-Jun-19 08:44

Well, yes, if things fail on a new database, better to first figure out why they fail there, and only then move to migrating data from the old database.

For permissions, users reading data have to be able to read all of mfdXXX tables, users sharing new components have to be able to read/write mfd_root and create new tables, users writing specific components have to be able to read/write mfd_root and mfdXXX tables for their components. It might be simpler to connect as the user who has permissions to do everything, make sure everything works, then reduce permissions gradually.

mikedufty

845 post(s)
#21-Jun-19 13:46

I think I've found my problem.

We've been using the 2009 psqlodbc driver from when we first set up the database.

I remembered I might have installed a newer driver on my home machine.

I switched to a current driver on my work machine and now it seems to all work.

Looks like the inability to share drawings from home is a separate issue - possibly related to the vpn firewalls or something. I only recently got server console working over vpn at all and don't think I had tested sharing of new components.

Manifold User Community Use Agreement Copyright (C) 2007-2019 Manifold Software Limited. All rights reserved.