FAQ
Hi All,

I have a huge database working on a debian linux machine, this machine
is going to get a hard drive wipe and new OS. I have another debian
linux machine (exact hardware + software). Is there a way to get
database "migrated" from machine 1 to machine 2 without much work?

Anxiously waiting for reply. Kindly advice.

Thanks in advance.

Hrishi

Search Discussions

  • Jeff Trout at May 27, 2005 at 4:52 pm

    On May 27, 2005, at 12:10 PM, Hrishikesh Deshmukh wrote:

    Hi All,

    I have a huge database working on a debian linux machine, this machine
    is going to get a hard drive wipe and new OS. I have another debian
    linux machine (exact hardware + software). Is there a way to get
    database "migrated" from machine 1 to machine 2 without much work?
    2 methods

    1. since it is the same kind of hardware you can shut down PG on
    machine1 and tar up $PGDATA move to machine2, untar and fire up PG.

    2. pg_dump on machine 1, copy the dump, load up on machine2. (this
    will take longer)
  • Robert Treat at May 27, 2005 at 5:28 pm

    On Friday 27 May 2005 12:52, Jeff Trout wrote:
    On May 27, 2005, at 12:10 PM, Hrishikesh Deshmukh wrote:
    Hi All,

    I have a huge database working on a debian linux machine, this machine
    is going to get a hard drive wipe and new OS. I have another debian
    linux machine (exact hardware + software). Is there a way to get
    database "migrated" from machine 1 to machine 2 without much work?
    2 methods

    1. since it is the same kind of hardware you can shut down PG on
    machine1 and tar up $PGDATA move to machine2, untar and fire up PG.
    Depending on your systems, it might be quicker to just mount one of the drives
    on the other machine and copy it directly over, bypassing the tar step.
    2. pg_dump on machine 1, copy the dump, load up on machine2. (this
    will take longer)
    Option 3 would be to use slony, which would minimise the down time, but might
    not fall into the "without much work" constraint.

    --
    Robert Treat
    Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
  • Zlatko Matic at May 27, 2005 at 5:33 pm
    Hi everybody!

    Recently I was struggling with client/server issues in MS Access/PostgreSQL
    combination.
    Although Access is intuitive and easy to use desktop database solution, many
    problems appear when someone is trying to use it as front-end for real
    server database systems such as PostgreSQL or MySQL.
    One of these problems is regarding pass-through queries and parameters.
    I wanted to have all the code on client, while executing it on the server in
    order to increase performance and speed. Therefore I created pass-through
    queriers for my forms and reports. The problem was that I couldn't pass
    parameters for where clause criteria, such as start and end-date. Therefore
    I have written procedure that passes parameters to pass-through queries.
    I hope it will help to those dealing with the same problem...

    For this method we use 2 saved pass-through queries.First, we have query
    with parameter name included in code in criteria expression. Then, we have
    another query which SQL string is generated from the first one. The SQL
    string is refreshed each time before query execution, so that parameter name
    is replaced with actual value. The form is based on that executive
    pass-through query...

    '------------------------------------------------------------
    ' This code has a list of saved pass-through queries along with
    parameters.and can be called
    ' on Click event.
    ' Theprocedure calls function ParametersToQueries () that recreates SQL
    string of executive query.
    ' written by: Zlatko Matic
    '------------------------------------------------------------
    Sub QueriesAndParameters ()

    Dim ws As DAO.Workspace
    Dim db As DAO.DATABASE
    Dim QueryName As String
    Dim NumberOfParameters As Integer

    On Error GoTo ErrorHandler

    DoCmd.Hourglass True

    Set ws = DBEngine(0)
    Set db = CurrentDb

    'List of queries and parameters...For example:

    QueryName = "SomeQuery"
    NumberOfParameters = 3
    ' Transfer name of the query and parameters to funtion
    ParametersToQuery
    Call ParametersToQuery (QueryName, NumberOfParameters, _
    "StartDate", Format([Forms]![MenuForm]![START_DATE], "yyyy-mm-dd"),
    _
    "EndDate", Format([Forms]![MenuForm]![END_DATE], "yyyy-mm-dd"), _
    "Option", [Forms]![MenuForm]![OPTION])

    Exit:

    DoCmd.Hourglass False
    Exit Sub

    ErrorHandler:

    Dim strErr As String

    strErr = "VBA-Error Information" & vbNewLine
    strErr = strErr & "Number: " & vbTab & vbTab & Err.Number & vbNewLine
    strErr = strErr & "Description: " & vbTab & Err.Description & vbNewLine
    strErr = strErr & "LastDLLError: " & vbTab & Err.LastDllError &
    vbNewLine
    strErr = strErr & vbNewLine
    MsgBox strErr, vbOKOnly + vbExclamation, "Error"

    Resume Exit

    End Sub

    Here is the code for function ParametersToQuery:
    '------------------------------------------------------------
    ' This function recreates SQL string of executive pass-through query
    ' written by: Zlatko Matic
    '------------------------------------------------------------
    Function ParametriziranjePstUpita(QueryName As String, NumberOfParameters As
    Integer, ParamArray Parameters () As Variant)

    Dim ws As DAO.Workspace
    Dim db As DAO.DATABASE
    Dim qdf As DAO.QueryDef
    Dim strSQL As String
    Dim strConnect As String
    Dim PstQueryName As String
    Dim n As Integer
    Dim x As Integer
    Dim ParameterName As Variant
    Dim ParameterValue As Variant
    Dim Parameter As Variant

    On Error GoTo ErrorHandler

    DoCmd.Hourglass True

    Set ws = DBEngine(0)
    Set db = CurrentDb

    PstQueryName = QueryName & "_prm"

    'Open thempass-through query to extract SQL string
    Set qdf = db.QueryDefs(PstQueryName)
    strSQL = qdf.SQL
    strConnect = qdf.Connect
    'Creation of new SQL string
    'Assign parameters
    If NumberOfParameters > 0 Then
    x = 0
    For n = 0 To ((NumberOfParameters * 2) - 1) Step 2
    ParameterName = Parameters (n)
    ParameterValue = Parameters (n + 1)
    strSQL = Replace(strSQL, ParameterName, ParameterValue)
    x = x + 1
    Next n
    End If

    qdf.Close

    'Assignig of changed SQL string to executive pass-through query
    If ObjectExists(acQuery, QueryName) Then
    'If executive query exists, open it
    Set qdf = db.QueryDefs(QueryName)
    qdf.Connect = strConnect
    Else
    'If executive pass-thrpough query doesn't exist, create it
    Set qdf = db.CreateQueryDef(QueryName)
    qdf.Connect = strConnect
    qdf.ODBCTimeout = 0
    qdf.ReturnsRecords = True
    End If
    'Set SQL string
    qdf.SQL = strSQL

    qdf.Close

    Exit:

    DoCmd.Hourglass False
    Exit Function

    ErrorHandler:

    Dim strErr As String

    strErr = "VBA-Error Information" & vbNewLine
    strErr = strErr & "Number: " & vbTab & vbTab & Err.Number & vbNewLine
    strErr = strErr & "Description: " & vbTab & Err.Description & vbNewLine
    strErr = strErr & "LastDLLError: " & vbTab & Err.LastDllError &
    vbNewLine
    strErr = strErr & vbNewLine
    MsgBox strErr, vbOKOnly + vbExclamation, "Error"

    Resume Exit

    End Function


    Function ObjectExists(ObjType As Integer, objName As String) As Boolean
    'Purpose: Determines whether or not a given object exists in database
    'Example: If ObjectExists(acTable, "tblOrders") then ...

    On Error Resume Next
    Dim db As DATABASE
    Dim strTemp As String, strContainer As String
    Set db = CurrentDb()

    Select Case ObjType
    Case acTable
    strTemp = db.TableDefs(objName).Name
    Case acQuery
    strTemp = db.QueryDefs(objName).Name
    Case acMacro, acModule, acForm, acReport
    Select Case ObjType
    Case acMacro
    strContainer = "Scripts"
    Case acModule
    strContainer = "Modules"
    Case acForm
    strContainer = "Forms"
    Case acReport
    strContainer = "Reports"
    End Select
    strTemp = db.Containers(strContainer).Documents(objName).Name
    End Select

    ObjectExists = (Err.Number = 0)
    End Function
  • Zlatko Matic at May 29, 2005 at 10:42 am
    Hello.

    I must have audit trail of all insert/update/delete on several table. I have
    several questions regarding that:

    1. Is it better to have one audit trail table that collects
    insert/update/delete of all audited tables, or it is better to have separate
    audit trail table for every audited table ?
    2. To use triggers or rules ? Example for both ?
    3. Could someone give me an example of a successfull audit trail solution ?

    I'm running on lack of time, so any help would be precious...

    Thanks.
  • Bob at May 29, 2005 at 4:10 pm
    Sorry for short message, but I'm headed out for the weekend.

    At my places of work we have use both a single table and one table for every
    table.

    I personally liked the single table for every table approach.
    On 5/29/05, Zlatko Matic wrote:

    Hello.

    I must have audit trail of all insert/update/delete on several table. I
    have
    several questions regarding that:

    1. Is it better to have one audit trail table that collects
    insert/update/delete of all audited tables, or it is better to have
    separate
    audit trail table for every audited table ?
    2. To use triggers or rules ? Example for both ?
    3. Could someone give me an example of a successfull audit trail solution
    ?

    I'm running on lack of time, so any help would be precious...

    Thanks.


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

    http://archives.postgresql.org
  • Mike Rylander at May 29, 2005 at 4:21 pm

    On 5/29/05, Zlatko Matic wrote:
    Hello.

    I must have audit trail of all insert/update/delete on several table. I have
    several questions regarding that:

    1. Is it better to have one audit trail table that collects
    insert/update/delete of all audited tables, or it is better to have separate
    audit trail table for every audited table ?
    2. To use triggers or rules ? Example for both ?
    3. Could someone give me an example of a successfull audit trail solution ?

    I'm running on lack of time, so any help would be precious...
    We use the "audit table per real table" approach. The SQL script to
    create the audit trail functions and triggers is attached. There are
    three example audit trail table creation calls right before the
    COMMIT.

    Hope that helps!

    --
    Mike Rylander
    mrylander@gmail.com
    GPLS -- PINES Development
    Database Developer
    http://open-ils.org
  • Zlatko Matic at May 30, 2005 at 9:15 am
    Hello, Mike!
    Your solution for audit trail is wonderfull! Easy and elegant !
    It helped me a lot and I successfully implemented it, with small
    modifications.

    Thanky you very much!


    ----- Original Message -----
    From: "Mike Rylander" <mrylander@gmail.com>
    To: "Zlatko Matic" <zlatko.matic1@sb.t-com.hr>
    Cc: "Postgresql-General" <pgsql-general@postgresql.org>
    Sent: Sunday, May 29, 2005 6:21 PM
    Subject: Re: [GENERAL] Audit trail ?

    On 5/29/05, Zlatko Matic wrote:
    Hello.

    I must have audit trail of all insert/update/delete on several table. I
    have
    several questions regarding that:

    1. Is it better to have one audit trail table that collects
    insert/update/delete of all audited tables, or it is better to have
    separate
    audit trail table for every audited table ?
    2. To use triggers or rules ? Example for both ?
    3. Could someone give me an example of a successfull audit trail solution
    ?

    I'm running on lack of time, so any help would be precious...
    We use the "audit table per real table" approach. The SQL script to
    create the audit trail functions and triggers is attached. There are
    three example audit trail table creation calls right before the
    COMMIT.

    Hope that helps!

    --
    Mike Rylander
    mrylander@gmail.com
    GPLS -- PINES Development
    Database Developer
    http://open-ils.org

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedMay 27, '05 at 4:10p
activeMay 30, '05 at 9:15a
posts8
users6
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase