Hami 網誌

轉貼 HOWTO: smooth CVS to SVN migration (and back again)

This page explains how I migrated the VideoLAN CVS repositories to Subversion while still allowing anonymous CVS access for users who did not want to move to Subversion. If you are a CVS user and have not yet fallen in love with Subversion, I suggest you have a look at this excellent project. In fact, I recommend to be familiar with Subversion before reading this document, because I may have missed important things.

The idea is to migrate the CVS repository to a Subversion repository using cvs2svn, disable CVS accounts (except read-only accounts such as anonymous) and set up post-commit hooks to replicate SVN commits back to the CVS repository.

First step: `cvs2svn`

Here are the preparatory steps to migrate a CVS module wooyay from the CVS root /cvs/stuff to a new SVN repository /svn/wooyay:

$ svnadmin create /svn/wooyay
             $ cvs2svn -s /svn/wooyay /cvs/stuff/wooyay
             $ svn ls file:///svn/wooyay
             branches/
             tags/
             trunk/
             $

That’s all! Your SVN repository is created. The default layout is a bit special but quite handy: tags are in tags/, branches are in branches/, and HEAD is trunk.

Don’t forget to backup your old CVS tree! It might be useful if something ever gets wrong.

Repository cleaning

Now that your repository is created, you can use Subversion’s magical powers to do whatever you want to the repository, such as removing and renaming branches or tags. These steps are not mandatory but you might find them convenient.

CVS branch names cannot start with a digit or contain periods, and you end up with branches called v1_2_3 instead of 1.2.3. And I don’t like that. Here is an example of what I would do:

$ svn ls file:///svn/wooyay/branches
             test/
             v1_0/
             v2_0/
             $ svn rm file:///svn/wooyay/branches/test -m "removed branch"
             $ svn mv file:///svn/wooyay/branches/v1_0 file:///svn/wooyay/branches/1.0 -m "renamed branch"
             $ svn mv file:///svn/wooyay/branches/v2_0 file:///svn/wooyay/branches/2.0 -m "renamed branch"
             $ svn ls file:///svn/wooyay/branches
             1.0/
             2.0/
             $

I also like to import .cvsignore files to Subversion properties and set the "Id" keyword properties for files containing the "$Id:" special string. If your repository is big, you might want to do this change only for trunk/ and the still active branches.

$ svn checkout file:///svn/wooyay/trunk workingdir
             $ cd workingdir/
             $ find -name .cvsignore | while read file; do
                 svn propset svn:ignore "`cat "$file"`" "`echo "$file" | sed 's,/[^/]*$,,'`"
               done
             $ find . -type f -a '(' -path '*/.*' -prune -o -print ')' | while read file; do
     if grep -q '\$Id:' "$file" && ! svn propget svn:keywords "$file" | grep -q '^Id$'; then svn propset svn:keywords Id "$file"; fi
               done
             $ svn commit -m "imported svn:ignore and svn:keywords properties"
             $

The post-commit hook

This is the important part. My svn_cvsinject script can be used to reinject SVN commits into the old CVS directory. Use option -r to specify the revision to reinject, and -a to do branch aliases. In our example, this would be the contents of the /svn/wooyay/hooks/post-commit file:

#!/bin/sh
             REPOS="$1"
             REV="$2"
             svn_maillog "$REPOS" "$REV" "svn@localhost" "sam@localhost"
             svn_cvsinject -r "$REV" "$REPOS" "/cvs/stuff/wooyay" \
                 -a "1.0/v1_0" -a "2.0/v2_0" &
             exit 0

It is advisable to run svn_cvsinject in the background because it can take a long time to finish. Also, make sure that all users with commit rights (including the user svnserve might run as) have write permissions on the CVS repository.

Here are the current svn_cvsinject features:

support for file creation and removal
support for directory creation and removal
support for simultaneous commits in different branches
support for branch aliases

However, it also has the following current limitations:

no support for new branches
no user mapping when run from svnserve (but the user is mentioned in the commit log)
concurrent calls may break things (use locks)
poor error handling

Conclusion

It works for me (tm), but I’d be happy to learn of other successful installations. And please tell me of failures as well, so that I can fix bugs!

If your CVS repository ever gets corrupted, you can reinject every SVN commit by restoring your backuped CVS tree and calling svn_cvsinject again for every revision since you used cvs2svn

docbook 配合自訂的 css 輸出

以 ubuntu 為例:

修改

/usr/share/sgml/docbook/stylesheet/dsssl/modular/html/docbook.dsl

...
(define all-element-number
  (external-procedure "UNREGISTERED::James Clark//Procedure::all-element-number"))
; 從這開始
(define %stylesheet%
  "docbook.css")

(define %stylesheet-type%
  "text/css")
; 到這結束
(root
 (make sequence ...
...

撰寫 docbook.css ,放在目標文件下

參考文件 :

http://www.powermag.com.tw/docbook/book1.htm

unable to load php_ldap.dll on php5.x

php_ldap.dll needs the following dlls:

ssleay32.dll
libeay32.dll

Copy them to the system32 directory.

20 pro tips

Tips and tricks you should be using to give your work that all-important professional edge.

http://www.netmag.co.uk/zine/design-tutorials/20-pro-tips

lifetype 使用者註冊問題

lifetype 對使用者的帳號採取較嚴格的限制,僅允許 A-Z a-z 0-9 的字元做為帳號，如要增加字元範圍可修改:

class/data/validator/usernamevalidator.class.php

define( "ONLY_ALPHANUMERIC_REGEXP","^([A-Za-z0-9]*)$" );

OPENLDAP 備份與還原

backup_ldap.sh 將每日 ldap 備份出來

#!/bin/bash

the_date=`date '+%m-%d-%y'`

the_dc='dc=tcc'

the_name=/var/ldap_backup/ldap_backup_$the_date.bz2

/usr/sbin/slapcat -f /etc/openldap/slapd.conf -b "$the_dc" | bzip2 -9 > $the_name

還原:

bzip2 -d /var/ldap_backup/ldap_backup_11-07-05.bz2

slapadd -c -l /var/ldap_backup/ldap_backup_11-07-05

利用 Web Editor 寫網誌

利用 Web Editor 寫網誌,參考網址

http://plog.yejh.tc.edu.tw/index.php?op=ViewArticle&articleId=426&blogId=1

FortiGate-400研習教材

bubble's weblog 的 FortiGate-400研習教材

PostgreSQL Performance Tips

取自:

http://chery.axonpro.sk/pgsql/chapters/performance/index.html

PostgreSQL Performance Tips

POSTGRESQL is an object-relational database under active development on the Internet. You can learn more by visiting http://www.postgresql.org.

In an ideal world, users would never need to be concerned about performance. The system would tune itself. Unfortunately, we do not live in an ideal world. An untuned database can be thousands of times slower than a tuned one, so it pays to take steps to improve performance. This article shows you how to get the optimal performance from your database.

Indexes

When accessing a table, POSTGRESQL normally reads from the beginning of the table to the end, looking for relevant rows. With an index, it can quickly find specific values in the index, then go directly to matching rows. In this way, indexes allow fast retrieval of specific rows from a table.

For example, consider the query SELECT * FROM customer WHERE col = 43. Without an index, POSTGRESQL must scan the entire table looking for rows where col equals 43. With an index on col, POSTGRESQL can go directly to rows where col equals 43, bypassing all other rows.

For a large table, it can take minutes to check every row. Using an index, finding a specific row takes fractions of a second.

Internally, POSTGRESQL stores data in operating system files. Each table has its own file, and data rows are stored one after another in the file. An index is a separate file that is sorted by one or more columns. It contains pointers into the table file, allowing rapid access to specific values in the table.

POSTGRESQL does not create indexes automatically. Instead, users should create them for columns frequently used in WHERE clauses.

To create an index, use the CREATE INDEX command, as shown in this figure. test=> CREATE INDEX customer_custid_idx ON customer (customer_id); CREATE

In this example, customer_custid_idx is the name of the index, customer is the table being indexed, and customer_id is the column being indexed. Although you can use any name for the index, it is good practice to use the table and column names as part of the index name--for example, customer_customer_id_idx or i_customer_custid. This index is useful only for finding rows in customer for specific customer_ids. It cannot help when you are accessing other columns, because indexes are sorted by a specific column.

You can create as many indexes as you wish. Of course, an index on a seldom-used column is a waste of disk space. Also, performance can suffer if too many indexes exist, because row changes require an update to each index.

It is possible to create an index spanning multiple columns. Multicolumn indexes are sorted by the first indexed column. When the first column contains several equal values, sorting continues using the second indexed column. Multicolumn indexes are useful only on columns with many duplicate values.

The command CREATE INDEX customer_age_gender_idx ON customer (age, gender) creates an index that is sorted by age and, when several age rows have the same value, then sorted on gender. This index can be used by the query SELECT * FROM customer WHERE age = 36 AND gender = 'F' and the query SELECT * FROM customer WHERE age = 36.

The index customer_age_gender_idx is useless if you wish to find rows based only on gender, however. The gender component of the index can be used only after the age value has been specified. Thus, the query SELECT * FROM customer WHERE gender = 'F' cannot use the index because it does not place a restriction on age, which is the first part of the index.

Indexes can be useful for columns involved in joins, too. They can even be employed to speed up some ORDER BY clauses.

To remove an index, use the DROP INDEX command. See the CREATE_INDEX and DROP_INDEX manual pages for more information.

Unique Indexes

Unique indexes resemble ordinary indexes, except that they prevent duplicate values from occurring in the table. This figure shows the creation of one table and a unique index. test=> CREATE TABLE duptest (channel INTEGER); CREATE test=> CREATE UNIQUE INDEX duptest_channel_idx ON duptest (channel); CREATE test=> INSERT INTO duptest VALUES (1); INSERT 130220 1 test=> INSERT INTO duptest VALUES (1); ERROR: Cannot insert a duplicate key into unique index duptest_channel_idx

The index is unique because of the keyword UNIQUE. The remaining queries try to insert a duplicate value, but the unique index prevents this and displays an appropriate error message.

Sometimes unique indexes are created only to prevent duplicate values, not for performance reasons. Multicolumn unique indexes ensure that the combination of indexed columns remains unique. Unique indexes do allow multiple NULL values, however. Unique indexes both speed data access and prevent duplicates.

CLUSTER

The CLUSTER command reorders the table file to match the ordering of an index. This specialized command is valuable when performance is critical and the indexed column has many duplicate values.

For example, suppose the column customer.age has many duplicate values, and the query SELECT * FROM customer WHERE age = 98 is executed. An index on age allows rapid retrieval of the row locations from the index. If thousands of matching rows exist, however, they may be scattered in the table file, requiring many disk accesses to retrieve them. CLUSTER reorders the table, placing duplicate values next to each other. This speeds access for large queries accessing many duplicate values.

CLUSTER even helps with range queries like col >= 3 AND col <= 5. The command places these rows next to each other on disk, speeding indexed lookups.

In addition, CLUSTER can also speed ORDER BY processing. See the CLUSTER manual page for more information.

VACUUM

When POSTGRESQL updates a row, it keeps the original copy of the row in the table file and writes a new one. The original row, marked as expired, is used by other transactions still viewing the database in its prior state. Deletions are similarly marked as expired, but not removed from the table file.

The VACUUM command removes expired rows from the file. In the process, it moves rows from the end of the table into the expired spots, thereby compacting the table file.

You should run VACUUM periodically to clean out expired rows. For tables that are heavily modified, it is useful to run VACUUM every night in an automated manner. For tables with few modifications, VACUUM should be run less frequently. The command exclusively locks the table while processing.

You can run VACUUM in two ways. Using VACUUM alone vacuums all tables in the database. Using VACUUM tablename vacuums a single table.

VACUUM ANALYZE

The VACUUM ANALYZE command resembles VACUUM, but also collects statistics about each column's proportion of duplicate values and the maximum and minimum values. POSTGRESQL uses this information when deciding how to efficiently execute complex queries. You should run VACUUM ANALYZE when a table is initially loaded and when a table's data changes dramatically.

The VACUUM manual page shows all of the VACUUM options.

EXPLAIN

EXPLAIN causes POSTGRESQL to display how a query will be executed, rather than executing it. As an example, this figure shows a SELECT query preceeded by the word EXPLAIN. test=> EXPLAIN SELECT customer_id FROM customer; NOTICE: QUERY PLAN: Seq Scan on customer (cost=0.00..15.00 rows=1000 width=4) EXPLAIN

In the figure, POSTGRESQL reports a sequential scan will be used on customer, meaning it will read the entire table. The cost is an estimate of the work required to execute the query (the numbers are only meaningful for comparison). The rows indicates the number of result rows expected. The width is the number of bytes per row.

The next figure shows more interesting examples of EXPLAIN. test=> EXPLAIN SELECT customer_id FROM customer WHERE customer_id = 55; NOTICE: QUERY PLAN: Seq Scan on customer (cost=0.00..22.50 rows=10 width=4) EXPLAIN test=> VACUUM ANALYZE customer; VACUUM test=> EXPLAIN SELECT customer_id FROM customer WHERE customer_id = 55; NOTICE: QUERY PLAN: Seq Scan on customer (cost=0.00..17.50 rows=1 width=4) EXPLAIN test=> CREATE UNIQUE INDEX customer_custid_idx ON customer (customer_id); CREATE test=> EXPLAIN SELECT customer_id FROM customer WHERE customer_id = 55; NOTICE: QUERY PLAN: Index Scan using customer_custid_idx on customer (cost=0.00..2.01 rows=1 width=4) EXPLAIN test=> EXPLAIN SELECT customer_id FROM customer; NOTICE: QUERY PLAN: Seq Scan on customer (cost=0.00..15.00 rows=1000 width=4) EXPLAIN test=> EXPLAIN SELECT * FROM customer ORDER BY customer_id; NOTICE: QUERY PLAN: Index Scan using customer_custid_idx on customer (cost=0.00..42.00 rows=1000 width=4) EXPLAIN

The first EXPLAIN shows a SELECT with the restriction customer_id = 55. The command reports another sequential scan, but the restriction causes POSTGRESQL to estimate that ten rows will be returned. A VACUUM ANALYZE command is then run, causing the next query to properly estimate that one row will be returned instead of ten. An index is created, and the query rerun. This time, an index scan is used, allowing POSTGRESQL to go directly to the rows where customer_id equals 55. The next EXPLAIN shows a query with no WHERE restriction. POSTGRESQL realizes that the index is useless and performs a sequential scan. The last query has an ORDER BY that matches an index, so POSTGRESQL uses an index scan.

Even more complex queries can be studied using EXPLAIN, as shown in this figure. test=> EXPLAIN SELECT * FROM tab1, tab2 WHERE col1 = col2; NOTICE: QUERY PLAN: Merge Join (cost=139.66..164.66 rows=10000 width=8) -> Sort (cost=69.83..69.83 rows=1000 width=4) -> Seq Scan on tab2 (cost=0.00..20.00 rows=1000 width=4) -> Sort (cost=69.83..69.83 rows=1000 width=4) -> Seq Scan on tab1 (cost=0.00..20.00 rows=1000 width=4) EXPLAIN

In this example, tab1 and tab2 are joined on col1 and col2. Each table is sequentially scanned, and the result sorted. The two results are then merge joined to produce output. It also supports hash join and nested loop join methods. It chooses the join method it believes to be the fastest.

Summary

A variety of tools are available to speed up POSTGRESQL queries. Although their use is not required, they can produce huge improvements in query speed. Another article outlines more steps that database administrators can take to improve performance.

About the Author

Bruce Momjian is Vice-President of Database Development at Great Bridge, LLC, http://www.greatbridge.com and author of PostgreSQL: Introduction and Concepts, ©2001, Addison-Wesley, ISBN 0-201-70331-9. This article is taken from that book. The web site for the book is http://www.postgresql.org/docs/awbook.html.

postgresql FAQ

http://sraapowergres.com/en/newsletter/issue_01/faq.inc.html

This FAQ is geared for those people interested in the more common issues that one encounters with PostgreSQL. It will grow in time with every issue by adding 10 new FAQ's to our list. They have been tested against the 7.x version of PostgreSQL but they are equally valid for the 8.x series too.

Q:How do I datamine log files when the information loaded may already be present i.e. what do I do about data duplication?

One easy way is to upload the data into a table and then create a view using the DISTINCT clause that removes duplicate records:

CREATE TABLE mytable(comments text);
CREATE VIEW v_mytable AS SELECT DISTINCT ON(comments) oid,comments FROM mychild;
copy mytable from logfile.txt

You can now query the view v_mytable which returns only unique records:

SELECT comments FROM v_mytable ORDER BY oid;

Q:How do I get a date, either from the past or in the future that is relative to a particular one such as today's date?

There are two techniques demonstrated here. The first method involves using a variation of either of these two statements which returns the date 30 days (1 month) from today:

SELECT now() + '1 month'::interval;
SELECT now() + '30 days'::interval;

The second method involves using the function date_mii() which returns the same date as the above example:

SELECT date_mii(date(now()),-30);

Q:How do I load a file into a database?

One method of importing a file into a database is to take advantage of PostgreSQL's Large Object mechanism which can load form of text or binary file. Manipulating a large object can be carried out by psql the PostgreSQL console client.
The following is an example of an import. Successful imports always return the Object Identification number (oid).

psql -Umyuser -c "lo_import 'myphoto.png' 'a picture of me' " mydatabase

This is an example of an export and assumes the oid is 12345:

psql -Umyuser -c "lo_export '12345' 'myphoto.png' " mydatabase

One can also import and export a large object using the equivalent server-side function:

SELECT lo_import ('/tmp/latestnews.ogg');
 lo_import
-----------
    444611
(1 row)

Server side functions are restricted in that they can only be used by the superuser. It can only access files on the machine where the server is running and the superuser has permission to access (under normal circumstances this excludes home accounts).

Q:Isn't there a way of loading a binary file into a record just like normal data without using Large Objects?

As a matter of fact yes. All programming languages such as perl, tcl, python, php etc, with the necesary hooks into libpq, can insert data directly using ordinary SQL INSERT commands.
Here's an example using the psql client, with a binary file called "mydocument.pdf".
Create a table called myfiles in a database called temp:

CREATE TABLE myfiles(mybinary text);

Log into the database:

psql -Upostgres temp

Convert and assign the contents of the binary file to base64, using "openssl", to the internal variable named "content":

set content ''' `openssl base64 -in mydocument.pdf` '''

You can now insert this binary into the table:

INSERT INTO VALUES myfiles(:content);

Q:How do I carry out an SSL session between client and server?

SSL connections are possible only when the capability has been expressly compiled into the client, such as the command line utility psql (using the --with-ssl switch). The postgreSQL server can accept one of three states of encrypted communications: none (no SSL required), dedicated SSL (only SSL sessions are accepted), detected (the server attempts SSL session but falls back to none if the client doesn't have any)
The settings in the files pg_hba.conf, postgresql.conf defines the type of socket connection.
Insert the following line in the file, postgresql.conf:

ssl = true

Define the client's authentication in the file, pg_hba.conf, as for example;

#TYPE      DATABASE   USER         IP-ADDRESS    IP-MASK         METHOD 
 hostssl   ALL        postgres     192.168.0.1   255.255.255.0   password
 hostnossl ALL        sraa_client  192.168.0.1   255.255.255.0   password
 host      ALL        sraa_admin   192.168.0.1   255.255.255.0   password

You can define a number of connections with a mix of encrypted and unencrypted sessions. For example, the first line insists on encrypted SSL session for the super user logging in from 192.168.2.0.1 while the second line permits the client sraa_client a non encrypted session coming from the same ip address. The third line tries first an SSL connection otherwise it switches over to a plain socket connection.

Q:What if I want an encrypted session and my client doesn't support it natively?

If you are communicating accross a network, you can still have an encrypted session by running an SSH encrypted tunnel between client and server. The method is to run "Port Forwarding" between the two computers.
In this example, a ssh client is used on the database client side while the SSH server is resident on the PostgreSQL server side. The client executes the following command to establish a connection and goes into the background once a successfull authentication has been made:

ssh -fN -L 6262:192.168.2.1:5432 user@192.168.2.1

The pg_hba.conf file must be configured to permit login. You must usethe server's own ip address, 192.168.2.1. Here's an example configuration instruction:

# IPv4-style local connections:
# TYPE  DATABASE  USER      IP-ADDRESS    IP-MASK     METHOD
  host  all       postgres  192.168.2.1   255.255.255 trust

The postgresql.conf file must contain the line:

tcpip_socket = true

This example login is made by the psql console client, although any client would work. It connects to port 6262 on the client localhost, where it will be forwarded to port 5432 on the remote server where the PostgreSQL server resides:

 psql -U postgres -h 127.0.0.1 -p 6262 template1

CAVEATS

The SSL authentication and connection is not handled by thePostgreSQL i.e. the server thinks it's a plain tcp socket connection, since it's the SSH session that's handling the encryption.
A SSH account is required on the server.
You can eliminate using passwords by using public-key cryptography (refer to the SSH man page) Verify that your authentication will function with port forwarding i.e. start with as simple authentication as possible such as "trust" and increase it from there.

Q:How do I bind the PostgreSQL server to a particular ip address?

There are two ways of assigning the ip address. The first method is to Assign the port number to the PGPORT environment variable in the same shell that the server starts up, for example;

#!/bin/bash
export PGPORT=6262
pg_ctl -d data start

The second method is to edit the postgresql.conf file:

tcpip_socket = true
port = 6262

Q:I've made changes to template1 but now I want to remove them. How do I accomplish this?

Every database cluster requires template1. The advantage of adding objects to template1 is that you can simplify the process of adding functions, tables, triggers, procedural languages etc to a number of databases by first adding them first here and then creating these new databases with the createdb utility or the SQL command CREATE DATABASE.
Removing these additions from template1 can be difficult if you don't remember all the changes.
There's two ways creating a clean template1 database. The first method is to obtain a datadump of template1 before any changes have been made to it:

pg_dump -c -Upostgres template1>template1.db

You can restore this data defintion back to the database as required:

psql -Upostgres -f template1.db template1

The second method involves creating a new database, using template0 as the TEMPLATE, and dumping its data definition to a file. Restore template1 using this newly created datadump in a manner similar to what has been outlined in the first example:

createdb -Upostgres -T template0 replacement
pg_dump -c -Upostgres replacement >template1.db
psql -Upostgres -f template1.db template1

Q:How do I effect changes made in the configuration files without shutting down the server and losing client connections?

Most Unix based distributions include server scripts permitting the system administrator to run the PostgreSQL server. For example, in a Debian, Linux, distribution (a System V operating system) we can use:

/etc/init.d/postgresql reload

Another method is to invoke the PostgreSQL server utility, pg_ctl, directly:

pg_ctl reload

Yet a third method is to send a signal to the server process directly. This can be accomplished by obtaining the process id, where you can get it by grepping the output from the ps utility, and sending a SIGHUP signal using the "kill" utility. If the postgres server had a process number of 13751 , you could reload the configuration files by typing the following command in a terminal:

kill -s HUP 13751

Q:How do I determine the size of a table or database?

There is an optional module available in the PostgreSQL "contributions" section that will return the size of any relation in a database, or even the database itself. Look in your postgres library directory for a file named "dbsize.so" to see if you have the module, otherwise you will need to download the complete PostgreSQL source, including the contributions source, and compile it yourself.
If the module is present you will need to create two functions, called "database_size" and "relation_size", to your database before you can use it. Carry out the following SQL commands in the database you are currently logged into (note: these functions will only work in the database where the following SQL commands have been invoked):

CREATE FUNCTION database_size (name) RETURNS bigint
    AS '$libdir/dbsize', 'database_size'
    LANGUAGE C WITH (isstrict);

CREATE FUNCTION relation_size (text) RETURNS bigint
    AS '$libdir/dbsize', 'relation_size'
    LANGUAGE C WITH (isstrict);

Here is an example invocation that returns the size of the database "template1":

SELECT database_size('template1');

Here is an example invocation that returns the size of the table "myrelation" in the currently logged in database:

SELECT relation_size('myrelation');

Hami 網誌

轉貼 HOWTO: smooth CVS to SVN migration (and back again)

First step: cvs2svn

Repository cleaning

The post-commit hook

Conclusion

docbook 配合 自訂的 css 輸出

unable to load php_ldap.dll on php5.x

20 pro tips

lifetype 使用者註冊問題

OPENLDAP 備份與還原

利用 Web Editor 寫網誌

FortiGate-400研習教材

PostgreSQL Performance Tips

PostgreSQL Performance Tips

postgresql FAQ

First step: `cvs2svn`

docbook 配合自訂的 css 輸出