Skip to main content

PostgreSQL on Windows - Presentation

I just completed a presentation for my local PostgreSQL User Group (the Portland PUG) and gave it last Thursday. I need to add some updates to clarify things and round out some of it, but here it is as I gave it.

Hopefully there are others who will find it useful.

As a side note, there are NOTES on some of the slides to go along with the material being presented.

[http://www.tbruce.com/Postgresql.odp]

I need to fix these URLs...They are not correct going forward.

Shrink / Delete a SQL Server Log File

When you want to delete a transaction log file (when you have more than one), you need to do the following steps:

ALTER DATABASE dbname SET RECOVERY SIMPLE
ALTER DATABASE dbname SET RECOVERY FULL

or

USE TimsDatabase
BACKUP LOG FileName WITH TRUNCATE_ONLY
GO
DBCC SHRINKFILE ( 'TimsDB_log1' )
GO
DBCC SHRINKFILE ( 'TimsDB_log2' )

GO

ALTER DATABASE TimsDB REMOVE FILE TimsDB_log2

That should then delete the log file. Don't forget to do a complete backup afterwards.

USE Master
GO
DECLARE @DEST AS VARCHAR ( 200 )
SELECT @DEST = 'Z:\SQL\Backup\TimsDB\TimsDB-' + CONVERT ( CHAR ( 10 ), GetDate(), 120 ) + '.BAK'
BACKUP DATABASE TimsDB TO DISK = @DEST
GO

That gives you a complete backup for additions to the Transaction Log.

There is a word of warning over this - There are not many times you would really want to shrink a Transaction Log. SQL Server expands it as it's needed. The only REAL time I've found it necessary is when there was a SIGNIFICANT amount of growth due to some special processing that was being done. This isn't something you would normally want to do as it does have a negative impact to performance when SQL Server has to expand the Transaction Log.

Table Size in Microsoft SQL Server

Sometimes I want to find out how many rows are in each user table in a SQL Server 2000 database. The following SQL helps me determine this.

SELECT
   'SELECT COUNT(*) AS ' ''' +
   RTRIM ( name ) + ''' ' +
   ' FROM ' + RTRIM ( name )
FROM
   sysobjects
WHERE
   type = 'U'
ORDER BY
   name;

Note: Running this on very large tables or in a database with lots of tables will have a significant negative impact on server performance. This forces a table scan of each table (reading each record in the table), which can really impact performance including flushing various system and database caches.

You can also directly query the statistics, but this is only an approximation and depends on how recently the statistics were updated (with DBCC UPDATE STATISTICS). Looking at the sysindexes table (again, this is SQL 2000), you can look at the rowcnt field and match the id field up against the sysobjects table. The following SQL may provide a good approximation of the rows in each user table.

SELECT
   o.name,
   i.name,
   i.rowcnt
FROM
   sysobjects o,
   sysidexes i
WHERE
   i.id = o.id AND
   o.type = 'U' -- User Tables ;

I have some better SQL I'll have to track down and add to this at some later date to show additional information. And of course, in SQL Server 2005 and SQL Server 2008 there are various Dynamic Management Views (DMVs) you can use to pull related data along with this information.

Copying files - preserving attributes

One way to do this is:

Supposedly, the following will work to copy a file (or group of files) from a directory and preserve the attributes (ownership and permissions).

$ cd /path/to/source/dir
$ find . | cpio -pdumv /path/to/destination/dir

The cpio command is a copy command designed to copy files into and out of a cpio or tar archive, automatically preserving permissions, times, and ownership of files and subdirectories.

An alternative is to use the "-p" option on the cp command.

See "cp --help" or "man cp" for further details.

Of course, don't forget to add the "-r" on as well, so it copies all files / directories recursively. So you'd use something like:

cp -rp /home/timbruce/data/ /media/backupdrive/timsdata/

Copying Files - Preserving Attributes

One way to do this is:

Supposedly, the following will work to copy a file (or group of files) from a directory and preserve the attributes (ownership and permissions).

$ cd /path/to/source/dir

$ find . | cpio -pdumv /path/to/destination/dir

The cpio command is a copy command designed to copy files into and out of a cpio or tar archive, automatically preserving permissions, times, and ownership of files and subdirectories.

An alternative is to use the -p option on the cp command.

The -p option of the cp command can be used to preserve attributes.

See cp --help or man cp for further details.

Of course, don't forget to add the -r on as well, so it copies all files / directories recursively. So you'd use something like:

cp -rp /home/timbruce/data/ /media/backupdrive/timsdata/

Default Route on OpenWRT

When I create a new linux virtual system and use OpenWRT at home to route out to the internet, I need to add a default route so that I can get out to the Internet. For some versions of linux, it doesn't do it automatically for me (or maybe it's the OpenWRT router and something I've misconfigured as it works for some systems, but not others?). The command to do this is:

route add default gw 192.168.8.8

Then to delete the route on one of the Virtual Machines, like pc036, I can use the command route delete, like this:

route delete default gw 192.168.230.11

VMWare automatically start at Boot on Linux

Before explaining how to start a virtual system on system start-up, I want to explain what services are, I'll continue with explaining the commands (for Linux), and then explain why you want a virtual system to start on the system start-up.

The term services, in geek speak, refers to two different, but related, things. A service could be the name of the actual application running on a computer or it could be used to refer in general to another computer or software package doing something for you (i.e, that application is functioning as a "service" for you to do something). This needs to be determined based upon context. The term server is similar. It could refer to an application that is doing something for a user (like the Apache WebServer service) or it could refer to a particular physical (or virtual) computer. (If you don't understand the concept of a virtual computer, I've provided other posts on the subject. However I'll sum it up as the computers today are so powerful and so under-utilitzed (in comparison to their capabilities) that you can “share” the hardware on a physical box with special software to mimic multiple computer systems). This is confusing to people who are not well versed in computers (i.e., users), and requires a lot of patience and open mindedness to follow through context.

When a computer starts up, it starts several services to do things on behalf of the user. For a Microsoft Windows computer, this could be something as simple as starting the network services so you can connect to another computer (like browse the Internet or get your email) and starting the anti-virus software or the firewall. However, if the service is a separate computer system, it may be to start the Email service so a group of users can read, download or send their email. Or it could be to start the Web service so uses can use their web browser to read information, browse pictures or watch a video (the content or information).

Like Apache (a Web Service) or Dovecot (software that services requests to view email by a client piece like Outlook), VMware is a service to simulate a computer or multiple other computers (see my other pages on VMware and Virtualization), and sometimes you want it to automatically start a virtual computer system when the VMware service starts on the host system. This can be accomplished by creating the following configuration:

VMWare Services

Many people think of VMWare as a way to reduce hardware costs by sharing resources on a computer system. And there are a variety of reasons to run VMWare. But VMWare is just another “service” on the computer... in this case the service mimics a whole computer system. So when the computer starts up, you may want the vmware service to automatically recover as well, and restart a select group of virtual machines when the physical hardware starts.

In reality, it's fairly simple to configure VMWare for this functionality. In this manner, if the system should fail for some reason (or patching of the system requires a restart), any virtual systems on the virtual host would automatically restart.

You can start a VMWare system during boot-up by the following crontab entry:

crontab -e

Then add the necessary entry:

@reboot /usr/bin/vmware-cmd "/path/to/my.vmx" start

Another way to solve this is to configure it inside the VMWare Server (aka VMWare GSX) application

On Microsoft Windows this is a little tougher, since the user account to configure it requires a password since the Virtual System may be running under a user account. (NOTE: Review if running it under Network Service or System Service if this is true!)

Why VMWare Service?

I explained earlier that an application could be a service. As an example, that the Apache Webserver is a service since a user or users connect to it in order to receive content (pictures, text, links, etc). But you may not want to run the Apache Web Service on your computer (for whatever reason). In this case you may want to build a virtual server that runs Apache. You could do this for testing a specific application configuration or you could do it because you are writing code and have not fully tested it or there may be security issues. In this case you could restrict access to the application / content or configuration (i.e., isolate it from other users). You may want this virtual machine to be ready for you soon after your computer (or another computer) starts. These are just a few examples, and you can certainly come up with more.

Conclusion

I've provided some background on services and hopefully cleared up some of the terms and usage. It's all dependent on context (or significant knowledge) as to what one person is talking about, but it is about an application or computer that does something on behalf of another user (or users). I explained how, under both Microsoft Windows and Linux, how to automatically have a virtual server start when the computer starts up. And I've wrapped up with a short description of how a VMWare server can support you.

Windows COMMAND.COM - Just Say NO

Most people I work with know that I spend a lot of time at the Command Line in both Windows and Linux. The Command Line Interface (CLI) gives me a lot of flexibility to quickly do things that I can't do through the Graphical User Interface (GUI).

However, a lot of users aren't aware that there are two different command line interpreters under Windows....and that using the wrong one can cause problems

People who started using Microsoft Windows 95 or Windows 98 only had one choice: COMMAND.COM. It was a 16-bit application (COMMAND.COM) that ran in a 16-bit environment. No big deal, and people now continue to use it out of habit. However, these same people now use Windows XP, which is a 32-bit Operating System, or in some cases a 64-bit Operating System.

Due to changes in the way the Operating System works in Windows XP (and Windows 2000 or Windows 2003), people couldn't run 16-bit applications. So Microsoft, as part of the Operating System, developed a 16-bit environment that those software programs could use. This environment, call ed the 16-bit NTVDM (NT Virtual DOS Environment), is shared by all 16-bit programs. What this means is that if any 16-bit program crashed or generated an error, it could affect any other 16-bit program also running and even cause it to crash as well. This is why using the wrong shell (COMMAND.COM) could cause problems.

The alternative is to use the program CMD.EXE anytime a use wants a command prompt. It has all the features of COMMAND.COM (and acts like it as well), plus it has additional features as well and does NOT run in the 16-bit environment.

In short, the next time you need a command prompt (shell), use CMD instead of COMMAND.

How to move your MySQL database directory

If you have a dedicated database server and the partition with the MySQL database fills up (or starts getting low), you can move your MySQL databases to a different location.

Let’s say you want to move the database to /home/mysql

  1. The first step is to stop MySQL so that all your data gets copied correctly.

$ /etc/rc.d/init.d/mysql stop

  1. Create the new database directory in the new location

$ mkdir /home2/mysql

(NOTE: At this point many people would use the move command (mv) instead of the copy command (cp). I ALWAYS copy the files… so that I can go back and undo my changes later!)

  1. Copy the database files from the first harddrive to the second harddrive

$ cp -R /var/lib/mysql/ /home/mysql

  1. Set the correct owner and group, permissions of the new database directory

$ chown -R mysql.mysql /home/mysql/

  1. Rename your old database directory

$ mv /var/lib/mysql/ /var/lib/mysql_old

  1. Create a symbolic link from the old database directory to the new one for any programs that rely on the default location

$ ln -s /home/mysql/ /var/lib/mysql

  1. Set the correct owner and group on the symbolic link

$ chown mysql.mysql /var/lib/mysql

  1. Edit the configuration file (/etc/my.cnf) to update the changes.

Comment out the old settings and add a line for the new one as you can see below

save your configuration files (my.cnf) and exit your text editor

  1. Restart MySQL

$ /etc/rc.d/init.d/mysql start

  1. Update the AppArmor configuration file (if necessary).
  2. If MySQL refuses to start look in /var/log/mysqld.log for the reason

Upgrading Drupal

I've built a check list for each time I upgrade Drupal, whether it's a simple (incremental) version upgrade (version 5.4 to 5.7) or a full version upgrade (from version 5 to version 6).

Upgrade

  • Copy the drupal and module files to the web server.
  • Unzip (using tar) the main drupal file.
  • Unzip the modules.
  • Configure the sites directory for each website.
  • Log into the website with the Administrative account (Account #1)
  • Disable all modules (/admin/build/modules)
  • Put database in maintenance mode
  • Backup database to flat file.
  • Update apache configuration to point to new version (commenting out the old lines - by commenting them out, you can easily uncomment them if you have to do a recovery and comment out the newer version).
  • Connect to the website url, adding update.php to the end (to start the update process).
  • Follow on-screen prompts.
  • Enable all modules
  • If you're using WyzWig module, Go back and enable module "Tiny mce" (enabling WyzWig disables it for some reason)
  • If you're using the node_permissions module, Run node_permissions module.
  • Re-run update.php
  • Configure the WyzWig Editor module (this seems to lose it's configuration with some upgrades, for some reason), and any other modules you need.
  • If you've added or upgrade any modules, fix the role permissions (/admin/user/permissions)
  • Take database out of maintenance mode.
  • Verify as an anonymous user (by logging out) that the site looks the way you want and that the functionality works correctly.

Recovery

  • Drop all tables in the database.
  • Reload the backup into the correct database.
  • Update apache to point to the correct directory (uncomment the old lines and comment the new lines).