Error loading table definition of a PostgreSQL table

When a PostgreSQL table name contains uppercase letters there is no known way to get the table definition SQL script.

When we right-click a table name in SQLPro for Postgres the has an uppercase letter and select Script as, CREATE to, New query window

we get the error message

/*
* Error loading table definition.
* ERROR: relation “public.users” does not exist
*/

As the error message tells us, PostgreSQL is looking for the table name with all lowercase letters.


The pg_dump utility acts the same way.

pg_dump -s -h MY_SERVER_ADDRESS -U M_USERNAME -t Users MY_DATABASE

The error message is

pg_dump: no matching tables were found


The same process works if the table name only contains lowercase letters.

Convert an existing PostgreSQL database to all lowercase table and column names

To avoid similar errors caused by upper case letters in table and column names, convert the existing database definition to all lowercase

    1. Backup your database from the command prompt
      PGPASSWORD=MY_PASSWORD pg_dump -h MY_DATABASE_ADDRESS -U MY_DATABSE_USER -f MY_BACKUP_FILENAME.bak MY_DATABASE_NAME
    2. Open the backup file in a text editor
    3. Add underscores to the table and column names to match the .NET entity framework user identity processes.
      1. The PostgreSQL driver converts the first letter of all lowercase table and column names to upper-case and the letter that follows an underscore. For example, asp_net_users will be converted to AspNetUsers.
        1. Replace the table names with TableName -> table_name ( leave __EFMigrationsHistory as is)
        2. Replace the column names with “ColumnName” -> “column_name”
        3. Replace the primary key names with PK_ -> pk_
        4. Replace the index names with IX_ -> ix_
        5. Replace the foreign key names with FK_ -> fk_
    4. Drop the database. In SQL Pro for Postgres
      1. Right-click the database and select Drop database
    5. Re-create the database. In SQL Pro for Postgres
      1. Rith-click the server name and select Create database
    6. Import the modified backup file from the command prompt
      PGPASSWORD=MY_PASSWORD psql -h MY_DATABASE_ADDRESS -U MY_DATABSE_USER -d MY_DATABASE_NAME -f MY_BACKUP_FILENAME.bak
    7. Reverse engineer the database back to the .NET data layer project
      1. Delete the class files that correspond to the database tables
      2. Delete the MY_DATABSEContext.cs file
      3. Import execute the command in the data layer project directory
        dotnet ef dbcontext scaffold --force "Host=MY_DATABASE_ADDRESS;Database=MY_DATABASE_NAME;Username=MY_DATABSE_USER;Password=MY_PASSWORD" Npgsql.EntityFrameworkCore.PostgreSQL

Copy files between Linux machines

The rsync command allows you to copy files using SSH connection, between your workstation and another Linux machine. You have to be logged into one of the machines, this command cannot copy files between two remote machines.

To copy a file from a remote server to your local workstation, execute

rsync -avz -e "ssh -i SSH_KEY_NAME" USER_NAME@SOURCE_IP:/SOURCE_DIRECTORY/SOURCE_FILE_NAME TARGET_DIRECTORY

To copy a file to a remote server, swap the source and target definitions after the SSH_KEY_NAME option.

Using tmux terminal multiplexer

The tmux terminal multiplexer allows us to open multiple terminal windows in the same SSH session and continue the command execution even when we log out of the SSH session. This way we can execute long-running copy commands overnight without keeping the SSH session open.

Install tmux

On CentOS family Linux

yum install tmux

To start tmux and attach to the last session

tmux a

To keep the current session active

To continue the session execution even when you log out of the server, detach from the session before closing the connection. See the commands below.

Commands

To switch to binding mode

ctrl-b

Commands in binding mode

d detach
% vertical split
horizontal split
arrows move between panes
c new window
p previous window
n next window
l last used pane
q display pane numbers
[ enable scroll with arrow keys or pgup, pgdown
q quit scroll mode
x close the pane (will ask you to press “y” to confirm)

Zooming

cmd-shift + zoom in
cmd-shift – zoom out
cmd-shift 0 (zero) 100% zoom

Copy files between Windows and Linux computers

There are many tools to copy files between Windows and Linux computers, I have found this method the simplest.

It does not require any software installation on the Windows machine, and only one package installation on the Linux machine.

Share a folder on the Windows machine

Share a folder on the Windows machine and allow access to it for a user. If the Windows computer is in the Windows domain, the domain user does not have to be a member of any security group on the Windows machine.

If you copy files from Windows to Linux, make the folder read-only for the user. If you copy files to the Windows machine, allow write access to the folder for the user.

Set up the Linux machine

  1. Install the cifs-utils on the Linux machine
    1. On Red Hat, CentOS, and Amazon Linux
      sudo yum install cifs-utils
    1. On Ubuntu
      sudo apt-get install cifs-utils

Mount the shared Windows folder on the Linux machine

  1. On the Linux machine create a directory to mount the Windows folder to
    mkdir /tmp/windows
  2. Mount the Windows share
    sudo mount.cifs '\\WINDOWS_SERVER_IP\attachments' /tmp/windows -o domain=MY_DOMAIN,username=MY_USERNAME,password=MY_PASSWORD,vers=1.0

    First, you will be asked for the root password on the Linux machine.
    If you do not specify your password in the line above, you will be also asked to enter your password on the Windows machine.

Access the Windows share

  1. On the Linux machine navigate to the mount directory
    cd /tmp/windows
  2. List the files of the Windows share
    ls -al

Troubleshooting

If you get the error message when you issue the mount command

mount error(16): Device or resource busy

try to unmount (umount !) the share first and try the mount again

umount /tmp/windows

 

delimited by end-of-file (wanted `EOF’)

When you create a file in a script and use an end of file delimiter,

if ...
  cat <<EOF> $chef_dir/attrib.json
    $json
  EOF
fi

and you indent the word EOF to look nicer, you will get the error message

warning: here-document at line … delimited by end-of-file (wanted `EOF’)
STDERR> …: line …: syntax error: unexpected end of file

To correct the script, move the EOF to the beginning of the line

if ...
  cat <<EOF> $chef_dir/attrib.json
    $json
EOF
fi

org.apache.jasper.JasperException: java.lang.IllegalStateException: No output folder

This error can happen on Linux and Windows, and the cause could be the same.

On Windows Server 2012R2 I opened a command prompt. To be able to delete a directory from the Tomcat webapps folder, I have issued the shutdown command.

When I started the server with the startup command, and navigated to my local website, my Java Spring MVC web application displayed the error message

org.apache.jasper.JasperException: java.lang.IllegalStateException: No output folder

The problem was, that the Tomcat service runs under root on Linux, and SYSTEM on Windows.

When you issue the startup command, Tomcat will run under the user account you logged in with.

Solution:

Stop and start the TomCat Linux or Windows service instead of issuing the shutdown and startup commands from the terminal or command prompt.

 

`pwd’: No such file or directory – getcwd (Errno::ENOENT)

When you rename a subdirectory under the directory where your Linux or MacOS terminal is open you may get the error message

/opt/chefdk/embedded/lib/ruby/gems/2.4.0/gems/chef-13.4.19/lib/chef/knife/cookbook_download.rb:45:in `pwd’: No such file or directory – getcwd (Errno::ENOENT)

You need to refresh the directory cache of the terminal.

  1. Go one level higher
    cd ..
  2. Go back to the directory
    cd MY_DIRECTORY

 

Migrate a WordPress web site to DreamHost

DreamHost is one of the recommended WordPress hosting providers. The DreamHost website has an extensive knowledge base, but the necessary steps to move your live site from another provider are scattered over multiple pages at https://help.dreamhost.com/hc/en-us/articles/214693178-How-to-manually-migrate-WordPress-to-DreamHost.

These steps will guide you through the streamlined migration process.

At your old provider

Create a site and database backup of your site at the old provider and save them on your workstation.

At DreamHost

Create an account at DreamHost.

If you arrive at the DreamHost site from https://www.cnet.com/web-hosting/ you get 50% discount on your initial hosting plan terms. The price will increase to the regular amount when you renew your hosting contract after 1 or 3 years.

It can take DreamHost an hour to activate your account. You can log into the control panel, but you have to wait for the account activation to be able to set up your site.

Create a fully hosted site

  1. In the DreamHost Panel select DOMAINS, Manage Domains and click the Add hosting to a Domain / Sub-Domain button
  2. Enter the name of your domain, select Remove WWW:… , and make a note of the Web username and Web server name.  When you will upload the site, make sure you use the newly created Web username and Web server (+.dreamhost.com) with the SFTP protocol. (FTP will be disabled for that user). When you save your changes, the new username and password will display on the screen. Click the Fully host this domain button.

  3. DreamHost creates a directory for the new user in the home directory on the web server and a subdirectory for the website.

Temporary domain name

If you want to keep your existing domain name at the original location until the new site goes live at DreamHost,  create a free temporary subdomain, MYDOMAIN.dreamhosters.com

  1. Return to the Manage Domains page and click the Add Hosting to a Domain / Sub-Domain button again,
  2. Scroll down to the Mirrored section. Enter a sub-domain name using dreamhosters.com, select your site from the drop-down, and click the Mirror this domain button.
  3. Open a web browser and navigate to MYDOMAIN.dreamhosters.com. You should see this page:

Create a MySQL database

  1. On the left side select GOODIES and MySQL Databases
  2. If you use the temporary domain name, you need to use it for the database hostname too. If you move an existing WordPress site, for the easier configuration, you can keep the same database name, username, and password that you had at the old site.
  3. Update the wp-config.php file on your workstation with the new values. Even if you keep the database name, username, and password values, the DB_HOST will be different, because the site is now at a different provider.
    define('DB_HOST', 'mysql.MY_DOMAIN_NAME.dreamhosters.com');

Import data into your database

  1. On the left side select GOODIES, MySQL Databases, and in the middle select phpMyAdmin next to the database name
  2. Make sure the SQL script on your workstation starts with the database selection command. If the instruction is missing, add the line:
    USE MY_DATABASE_NAME;

    For some reason, the MySQL Workbench cannot interpret the encoding of the SQL file, use a better text editor to edit the file, otherwise international characters in the data will be corrupted.

    The Microsoft SQL Server Management Studio is perfect for this purpose.

  3. On the Import tab click the Choose File button to select the database backup SQL file on your workstation and click the Go button

Update the site URL in the database to match the temporary website address

On the phpMyAdmin web page

  1. Select the Browse tab, on the left side select your WordPress database, and in the database click the wp_options table.
  2. Click the Edit link in the siteurl row. This will automatically open the Insert tab,
  3. Update the siteurl value in the wp_options table and click the Go button,

Upload the WordPress site files to the new web server

Use an FTP client to upload the files to the site. The site host user only has SFTP rights, so make sure you configure your FTP client accordingly. Use the web address, username, and password that you used, when you set up the fully hosted website above.

To rebalance the load between their web servers, DreamHost time-to-time moves websites from one server to the other. Use the subdomain we created above, to access the FTP directory, as it will always point to the correct web server.

  1. If you use FileZilla, create a new site for the web user, and select the SFTP protocol,
  2. Make sure you update the wp-config.php file with the new database connection values, and upload it to DreamHost,

Test your new site

The home page of the new site should be accessible at the MYDOMAIN.dreamhosters.com address we created above.

When you click on any of the post titles, you may get the following error message

Not Found

The requested URL /archives/… was not found on this server.

Additionally, a 404 Not Found error was encountered while trying to use an ErrorDocument to handle the request.

Update the permalinks

The WordPress database still contains the absolute locations of your HTML files and images. To refresh the permalinks of your site

  1. Log into your WordPress site,
  2. On the left side select Settings, Permalinks, and click the Save Changes button.
  3. After a few seconds the site should start to work.

Point your domain name to the new site

When you have successfully tested your new site, you can point your domain name to the DreamHost webserver. The DreamHost knowledge base will help you at https://help.dreamhost.com/hc/en-us/articles/215412347-Updating-your-DNS-after-your-WordPress-site-has-been-migrated-to-DreamHost

IMPORTANT: If you use your domain name to connect to Office 365 or other services, do not switch to the DreamHost name servers. Use “Option #2 — Pointing your A records” on the DreamHost knowledge base page above, to set the IP address of your website in the DNS record.

Configure WordPress to use the final domain name

To be able to test our site with the temporary subdomain we have updated the database. Now it is time to change the value back.

  1. Open the MySQL management utility
  2. Select the Browse tab, on the left side select your WordPress database, and in the database click the wp_options table.
  3. Click the Edit link in the siteurl row. This will automatically open the Insert tab,
  4. Update the siteurl value in the wp_options table and click the Go button,

Test your site with your domain name

The home page of your WordPress site should load without error. If you get the error again:

Not Found

The requested URL /archives/… was not found on this server.

Additionally, a 404 Not Found error was encountered while trying to use an ErrorDocument to handle the request.

Update the permalinks again. See Update permalinks above

Ubuntu gets stuck in a login loop

When I rebooted my Ubuntu machine it started to repeatedly ask for the password.

When I entered the correct password, the screen went black and asked for the password again.

The problem was an error in the ~/.profile file.

To log into the Ubuntu terminal

  1. Press the Ctrl-Alt-F3 key combination
  2. Enter the username
  3. Enter the password
  4. Open the ~/.profile file with
    /bin/nano ~/.profile

Make sure the

  • The syntax is correct, all open braces are closed,
  • All PATH= statements have :$PATH at the end.